Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Want To Add Dashes To Cell Text...


I have a column of city names, some are one word and others are two or more. I am trying to take the city name and add it to another word, add dashes in between them and make everything lower case. For example:

Column 1, Cell 1 is: San Diego

I'd like it to look like: books-san-diego (books being the keyword I'd like to insert before the city.)


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Look Up On Cell But Based On Value Between Dashes
Here is the examples of values i have

TCH1 - John Smith - I - REG - Total
AV - John Doe - D - REG - Total

what i want to do is lookup on the value after the first dash and before the second dash to get the names only ; John smith, john doe

View Replies!   View Related
Add Text Based On Part Of Another Cell Text
I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?

View Replies!   View Related
Add Text In One Cell If Text Is Present In Another
I have a macro which is identifying unique values between two sheets (sheets '4' and '5') then pasting these unique values at the end of the list (column C) on sheet '5'. It also drags down the formulas in columns D and E in sheet '5' as long as there is text in column C.

Does anyone know how i can add to the macro so that for every unique value added from sheet '4' to sheet '5' the word "uncategorised" is added to the respective cell in column F on sheet '5'.

I only want this to be for the new text added (the unique values form sheet '4') because column F will be blank for these cells.

View Replies!   View Related
Copy Data From Cell In One Worksheet, Add Text, And Paste To Cell In Another Sheet
I have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'.

Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.

View Replies!   View Related
Add Text From Specific Cell To Next Empty Cell In A Column
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.

View Replies!   View Related
Format Cell To Add Text To Begining Of Cell ()
I have a column with product codes. each code starts with "11P - " and is followed by a 6 digit number. Is there a way that I can format the cell so that have this automaticlly added when I type in the rest of the code. For example if I type "123456" the cell would display "11P - 123456"

I imagine that it is a matter of setting up a custom # format, but I have no clue how to do that.

View Replies!   View Related
If Cell Contains Chosen Text, Add Value From Another Cell In Same Row
I have a sheet with 65,000 rows. Column c contains text and numbers (phrases from search engine results seraches). Column G contains the number of times the phrase was searched. I am wanting to run a formula / macro which goes like;

If cell in column c contains variable then copy cell g out of same row to cell h. This way I can quickly sum up column h to see total number searches by variable. I have looked through forum for past half hour and could not find what i needed.

View Replies!   View Related
Add Cell Data/Text To Cell Comment
attached excel file .I need the data thats there from B4:E8 to be displayed on cell I9 as a comment .is there any way to do it ?

View Replies!   View Related
Remove All The Dashes
Have a column containing data which may have 1-3 dashes locates within
each cell value.
Such as:
125-457A
158-857-1116
124184A-1
125-8745-22D9
458-2145-002-7

Would like to remove all the dashes "unless" the last dash to the right
is followed by a just a single digit. The results would be as follows:

125457A
1588571116
124184A-1
125874522D9
4582145002-7

On the example data, it kept 2 of the dashes in the cell values,
because the were only one digit away from the extreme right, but
removed all others.

View Replies!   View Related
Add Text Before & After Each Cell
I have thousands of html files to create, however each one is only different by one string and those strings are already in excel in neat rows, so I'm trying to write a VBA script that will create the html files for me.

Let's say the html pages are made up of:

TOP:
<html>
<head>Stuff</head>
<h1>Title</h1>
<p>

***EXCEL VARIABLE***

BOTTOM:
</p>
</html>

This is where I'm trying to get to.

I've got this far:


Sub Run_Click()
Dim NoOfRows(1 To 500)
' Clear the Output spreadsheet of all contents
Worksheets(2). Cells.ClearContents

For NoOfRows = 2 To 500
If Worksheets(1).Cells(NoOfRows, 1).Value <> "" Then
Worksheets(2).Cells(NoOfRows, 1).Value = "test" & Worksheets(1).Cells(NoOfRows, 1).Value & "test"
End If


End Sub

View Replies!   View Related
Add Or Remove Text Within One Cell?
I need to format C2 so that it removes all text that comes after a dash in B2, but if no dash exists, then it returns the text: Parent. For example, here are 3 values in B2, B3, and B4:

1234-s
1234-m
1234

In C2, C3, and C4 I'd like to have the following values returned based on the above values in B:

1234-
1234-
Parent................

View Replies!   View Related
How To Add Two Text Cells Into One Cell
I tried to find this in the help section but was unable to locate it. Could you tell me how to add a name in one cell plus the name in another cell into one cell with the "&" sign between? So it would read "Don & Rose" in the final cell making the structure similar to:

Column A Column B
Row 1 Don Don & Rose Row 2 Rose

View Replies!   View Related
Add Cell Comment With Text
if choose a cell then the code works fine, but when i choose a selection of cells
(ex. A1 A2A3 A4) then the code fail to excute in this line: Invalid proceduer call or argument error

.AddComment "hmk :" & Chr(10) & " value was " & ActiveCell.Value

and then fail to excute in this line: typr mismatch error

Selection.Value = (Selection.Value) * r

now here, does have to be loop to solve

here is the full code

Sub test()
Dim r As Integer
ActiveCell.ClearComments

View Replies!   View Related
Return Cells That Have Exactly 3 Dashes
I have a column of data (column A) with cells that have IDs (alpha-numeric) that contain anywhere between 0 to 5 dashes. Since this column will change weekly, I want to dynamically pull only those cells that contain 3 dashes.

Example:
10648145-3026718-t526363536m
09wcg7-s1s89-wtpxm33-uu6jj0-9y8mv-v1
10658575-2349769-

I would only want to have returned 10648145-3026718-t526363536m since it contains 3 dashes. Do you know of a nifty formula for doing this? I think an array would get me the dynamic piece (so they are not spaced out).

View Replies!   View Related
Add Sheets & Name As Cell Text
I've got another question before Christmas. I want to insert a new worksheet into my workbook and rename it 1 to 52 (weeks ).I can do it with a macro quite easily ( sheet1), and rename that sheet with the week number I'm working on, but if a user creates two new worksheets in the one sitting (sheet2), I have a problem. This is the code I'm trying to use but it won't work after sheet 1. It won't read the " if then " statement and go to the next statement.

'add sheet ,rename
Sheets.Add
If Sheets("Sheet1").Select Then
Sheets("Sheet1"). Name = "01"
ElseIf Sheets("Sheet2").Select Then
Sheets("Sheet2").Name = "01"
ElseIf Sheets("Sheet3").Select Then
Sheets("Sheet3").Name = "01"
End If
End Sub .

Just a thought I had, is there a way to name a new sheet before it calls itself Sheet1 or Sheet 2 ???.

View Replies!   View Related
Format Cells With Dashes Between Alphanumerics
I'm trying to find a way to have excel 2003 format the data I put in a cell to auto insert dashes every five characters. I'm entering product keys for the software we have on hand, and it would be a little quicker if I could simply have excel auto format the data entered in to include dashes every five characters.

Example:
- I would enter: D5ATT3D28F6F44536489413E2 (This is a fake, non-working product key, I just typed in random numbers a letters.)

-Excel would format it like: D5ATT-3D28F-6F445-36489-413E2

View Replies!   View Related
How Do I Get The Dashes To Not Count As A Digit/number?
I have this script that does what i need it to do but one thing, I need the script to count the numbers as digits only and not the dashes in the format.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Len(Target) > 11) Or (Len(Target) < 10) Then
MsgBox "Not a valid number of characters, please try again"
Target.Value = vbNullString...................

View Replies!   View Related
Range Text Property To Add String To Cell
I don't understand what I am missing. but I can't paste a string into a cell [range.cells(x,y).text = string]

Here is my ' ' -delete any rows that contain "Fab 11"
' -rename any cell that has "Supply x" (Delete " supply x" from text)

Dim cellText As String
Dim i As Integer
Dim lastRow As Long
Dim rngSigac As Range
Dim wb As Workbook
Dim ws As Worksheet

View Replies!   View Related
Add Specific Number Of Spaces To Text In Cell
I've got a single column worksheet with a varying numbers of characters in each row.

At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.

I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:

Dim lngSpacesNeeded As Long

For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i

Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".

View Replies!   View Related
Delete All But 1 Worksheet, Add New Sheet & Name As Cell Text
I need to cycle and extract row data from the A, B and D columns and copy it into another worksheet while maintaining the copied data structure. Right now I am manually selecting the data....

View Replies!   View Related
Macro - Add Carriage Return To End Of Text In Cell
I am looking for some assistance in creating a macro that would allow me with one key stroke to enter a cell in edit mode, add a carriage return to the end of text in the cell, then move the cursor to the next cell.

View Replies!   View Related
Hyperlink Code For Sheet Names With Spaces Or Dashes
I believe I have 99% of what I need. The code below has one bug. (See just the underlined text for a very quick overview. The rest is detail.)

My goal is to generate a TOC that:
1) has the VBA run from my Personal Workbook.
2) can be run in any worksheet of any open workbook.
3) can be run in a worksheet of any name.
4) will insert itself at the currently active cell.
5) has entries hyperlinking to the referenced worksheets - of any name.

Right now, I'm stuck with the last point. The macro below will generate the TOC at the currently active cell. The TOC will be hyperlinks to the referenced worksheets.

However, if the referenced worksheet contains a space, dash, or other special character then the generated hyperlink is broken. For example, if the referenced worksheet is "sheet1" then the hyperlink generated works just fine. However, if the referenced worksheet is "sheet-1" then the generated hyperlink doesn't work.

Sub IndexList()
Dim objSheet As Object
Dim intRow As Integer
Dim strCol As Integer

Set objSheet = Excel.Sheets
intRow = ActiveCell.Row 'Start writing in active row
strCol = ActiveCell.Column 'Start writing in active column

For Each objSheet In ActiveWorkbook.Sheets
Cells(intRow, strCol).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
objSheet.Name & "!A1", TextToDisplay:=objSheet.Name
intRow = intRow + 1
Next
End Sub

View Replies!   View Related
Join Text & Wrap: Add A Figure To The First Part Of The Cell
This is not related to macro - it's an excel question regarding wrapped-around text within a cell (i.e.: text brought onto next line by <Alt>+<Enter>)...

SayI have the following in a cell :
Car<Alt>+<Enter>
Truck<Alt>+<Enter>
Van

and say I want to add a figure (in this example 1A) to the first part of the cell and then another to the second line of wrapped text and so on. How can I split the cell (or lookup the first 'wrapped' line) so that I can then perform changes (using formulas with '&' to combine etc or whatever) so that in the end cell have the following:

Car 1A<Alt>+<Enter>
Truck 2C<Alt>+<Enter>
Van 1B

View Replies!   View Related
On Cell Selection, In Named Range, Clear Content Or Add Text If Empty
I wrote some code that toggles bewtweeen the word "Yes" and the function clearcontents.

What it does not do, is if you click on cell A1 and change it's contents and you click on the same cell again it does nothing. You need to click on another cell say A2 before you can go and change A1 again.

My code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Documents")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Value = "Yes"
Else
Target.ClearContents
End If
Application.EnableEvents = True
On Error Goto 0
End If

End Sub

View Replies!   View Related
Add Cell Number To Date & Add Weeks
I want to add a numeric number eg: 4 to a date format eg: 15/08/2007 so that it calculates 4 WEEKS from 15/08/2007 and returns the CORRECT date in a date fomat itself. How do i do this through a VB code ?

View Replies!   View Related
Add Text To Text In A Range
I'm in the middle of a huge export cleanup from one of my companies DB's to excel. I want to enter a formula within a macro to change a cell's display. Example:

Column D, Row 2 lists the cell as "Extended Warranty" when I export the data. I need for it to say "extended warranty repair" after I run the clean up macro. There are 4 other instances as well. I was trying to write an if/or formula.

View Replies!   View Related
Static Date Stamp: Add A Date Stamp That Will Populate A Field When Text Is Entered Into Another Cell
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???

View Replies!   View Related
Custom Format Which Displayed "-"(Dashes Without Quotes) Insted Of "0"
i have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)

View Replies!   View Related
How To Add Three And Five Characters Between Text?
I've searched Excel's Help but still unsure how to do this. I hope excel experts here can lend me a hand...

I have literally thousands of words in a single columns column which looks like this:

absah
absen
absenan
absensi
abses
absis
absolusi
absolut
absolutisme
...
...
...
etc

My question is what formula if i'd have to enter to add three spaces between characters and five characters at every end of the word so it should look like this?:

a b s a h .
a b s e n .
a b s e n a n .
....
....
etc

Note i have entered a full stop at the end of the example above to make the five spaces visible in this demonstration (I dont need the period character inserted at all).

View Replies!   View Related
Add A Text Prefix
I have a colummn of data (text data). For my purposes I would need to add a prefix text to every single cell contained in that column; for instance, if I have a cell with a text "hellohowareyou" ,

I would need to change it by adding to it a constant prefixation text "es_ES@" so that the final result would be displayed like "es_ES@hellohowareyou" , and so on for the rest of the cells under the same column.

View Replies!   View Related
Macro Add Text
I would like a macro to do the following steps

This will be on Sheet 2

Copy tab name starting from Sheet3 and paste that text into cell B8, continue doing this down column B untill finishing all the tabs in the workbook (could vary from 3-30 tabs)

Then starting on sheet 3 copy cell C4 and paste it on Cell A8 of Sheet 2 untill finshing all the following sheets.

View Replies!   View Related
Add New Row And Text After Last
I have a spreadsheet with several item numbers that can appear one or more times and what I want is after the last time the item number appears is to add a new row under the item number and place the text Total in column B.

Example cell b2 is item number 123 b3 456 b4 456. After I run said macro b3 should be a new row with Total added to cell b3. And b6 would also be a new row with the text Total.

View Replies!   View Related
Add Text To Label
I have a textbox with a label & he will type in his name in the textbox & it appears on the label. Is there any way to put the word "Hi" on the label & then have his name appear after "Hi" on the label. I can do it using 2 labels side by side but I would like to have it all on 1 label

View Replies!   View Related
Add Text If Cells Has Certain Value
I need to look at 7 different cells and determine which one contains the value 18.

After determining which one does, I need to move over one column and add text.

If I can solve this then I will need to look up 2-3 other values and to add a different string of text for each one.

Sub Std_Events()

If J15 = 18 Then Range("K15").Select
ActiveCell.FormulaR1C1 = " Event #1"

If L15 = 18 Then Range("M15").Select
ActiveCell.FormulaR1C1 = "Event #1"

If N15 = 18 Then Range("O15").Select
ActiveCell.FormulaR1C1 = "Event #1"

View Replies!   View Related
Add Cells When Text In Some
I have a two formulas that I need to sum, =IF(V19<=0,"",IF(V19>0,V19)), =IF(V37<=0,"",IF(V37>0,V37)) The problem is, the sum of these formula will not always show a value, but I need to sum them in another field. I am using this formula; =T31+T13 but it is not working. Currently unless both formulas has a value, my sum field will show "#Value". I need to show the sum even if only one of the formulas has a value or if no value from both I need the cell to = blank.

View Replies!   View Related
Add Text To Entire List
I have an excel list in one column of all my AP vendors and I want to add text to the bottom of the vendors name, such as:

Currently looks like this:

Office Depot
Staples
Orchard Supply

I Would like it to look like this:

Office Depot
FY 09/10
Staples
FY 09/10
Orchard Supply
FY 09/10

So basically I want to add the FY 09/10 to the bottom for the entire list of vendors. On top of that, all though it's not a critical, I would like to have the FY 09/10 be a different size and boldness.

this is to do a mail merge for labels in word

View Replies!   View Related
Add 77 Plus Their Extension Text Phone
I have been able to successfully code a new email message one little thing I can't figure out is adding 77 plus their extension txtphone.

View Replies!   View Related
Add Formula To A Text Box
I am trying to insert a formula into a text box but I having problems with the "wording portion" of the formula.

I am trying to pull total sales from cell A1

Am I using this formula in the textbox

View Replies!   View Related
Add A Text Box To A Certain Sheet
I'm looking for a code in vba that I can add a text box to a certain sheet, let say to cell A1 and add text to that box.

View Replies!   View Related
Concatenate Cells And Add Text
I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.

Example:

Assume row 1 and columns A, B, C, D.

496 | 6d7 | Minor | .375 +/-.010

What I need this to read as one long string:

#496, 6d7, Minor, .375 +/-.010

Adding the text in Red.

Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.

View Replies!   View Related
Add Text To UserForm TextBox
I want to add variable content to a textbox in a userform. I want to duplicate a Msgbox as closely as possible to allow me to position the box properly, and I would like a procedure to add a prompt to the userform as you would to a Msgbox.
That is, I have found a solution using a cell in my spreadsheet, but I would like a fully vba oriented solution. In the message box you can say

MsgBox Prompt:="Put your message here."

For a textbox in a userform, I can link it to a cell (say A1) and then put data into the cell

Range("A1").Value = "Put your message here."

and it shows up in the text box when I do a

Userform1.Show................

View Replies!   View Related
Add Text To Status Bar
I am trying to use teh staus bar for simple reporting of progress. When I launch Excel 2003 on XP the status bar works as normal with Excel reporting progress etc.

When I take control in VBA using: Application.StatusBar = "some text here"

then the status bar text appears as solid black. As if each character is black on a black background. If the text is longer then the length of black increases. See attached image. I reduced the Excel window from maximised to allow me to print a small image of the window showing teh status bar. The status bar works as I would expect, running the macro I can see the progress. When I maximise it it reverts to being black on black!

View Replies!   View Related
Add Text To All Worksheets If Condition Met
I have a macro that is supposed to type a text in column 8 if condition is met, on all sheets but one (" Total"). When I run it, it stops at "Next Line" with a "Compile error: Next without For". For the life of me, I can't figure out where I'm missing a "For".

Sub ForceMatch()
Dim ws As Worksheet
Dim line As Integer
endline = Range("A1000").End(xlUp).Row

For Each ws In Worksheets
If ws.Name <> "Total" Then
With ws
For line = 3 To endline
If (Cells(line, 9).Value <= 5000 And Cells(line, 8).Value = "") Then
With Cells(line, 8).Value = "FORCE-MATCH CANDIDATE < $5K ICDP"
Next line
End With
End If
Next ws
End Sub

View Replies!   View Related
Add Space To Each Uppercase Letter In Text
I import a CSV file into Excel where the column title row has column titles that are just one long text string, without any spacing between the words. For example:
CompanySiteDescription
CompanySiteExternalSystemID
IssueNumber

I would like a method (formula or macro) that would add a space-character before each uppercase letter (that's not the first letter in the string or an uppercase letter that directly follows another upper case letter). Thus:
CompanySiteDescription becomes Company Site Description
CompanySiteExternalSystemID becomes Company Site External System ID
IssueNumber becomes Issue Number


View Replies!   View Related
Text String And I Want To Add A Number Automatically
I have in a cell a text string and I want to add a number automatically in the following rows. With other words

Product01
Product02
Product03
Etc
Product is a constant

How will I do this I know I can type the numbers and CONCATENATE it with the text part but I am too lazy to type out all the numbers to 1000

View Replies!   View Related
Add Missing Leading Zeros To Text
I have a spreadsheet full of site codes which should be the format:
AA1111

or

A1111

Where A = any alpha character & 1 = any numeric character

unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.

At the moment I manually correct this. Is there a better way to do this?

View Replies!   View Related
Locate Values And Add Text In The Same Row
I have created a form that will be used my many people. The first person will enter their information and click a button to transfer their data to a second workbook, as well as send the workbook via email. At a later time, someone else will add more data to the emailed form and transfer this to the database in the second workbook. What I need is to be able to find the row in the second workbook containing the data transferred the first time and add the data that was put in later to the same row as the original.

View Replies!   View Related
Add Up Numbers In Column Based On Text In Another
I quickly put together an inventory form on our company server where employees can essentially just enter part numbers, quantities, and a few other things, rather than write everything out. So what might happen is, an employee puts together a list of 10 items with their respective quantities, but a few days later, another employee pulls up the spreadsheet and needs to add a few more of a part already listed. Protocol for the company is to do a separate line, rather than add it to the line where the item is already listed. What I am trying to do is, create a separate sheet within that workbook, that simply lists the part numbers, in order, with their total quantity. I have attached a basic spreadsheet showing what I would want, with a tab at the bottom labeled, "What I want."

View Replies!   View Related
Add Text To Specific Colored Cells
I would like to write an If clause to do the following. If the cell is colored blue (color index 32), insert the text "ignore" in the cell

View Replies!   View Related
Add Or Subtract Depending On Text Value In Another Column
I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday. Sheet "remark9" is used to record detail of attendance for September. In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days. How to count from E2:AH2?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved