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
Related Forum Messages:
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?
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.
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.
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.
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.
Remove All The Dashes
Have a column containing data which may have 1-3 dashes locates within
each cell value.
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:
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.
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:
This is where I'm trying to get to.
I've got this far:
Dim NoOfRows(1 To 500)
' Clear the Output spreadsheet of all contents
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"
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:
In C2, C3, and C4 I'd like to have the following values returned based on the above values in B:
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
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
Dim r As Integer
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.
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).
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
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 Sub .
Just a thought I had, is there a way to name a new sheet before it calls itself Sheet1 or Sheet 2 ???.
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.
- 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
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...................
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
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))
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".
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.
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
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
objSheet.Name & "!A1", TextToDisplay:=objSheet.Name
intRow = intRow + 1
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 :
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:
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"
Application.EnableEvents = True
On Error Goto 0
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.
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:
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 .
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).
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.
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.
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.
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
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.
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"
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.
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:
I Would like it to look like this:
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
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.
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.
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
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!
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".
Dim ws As Worksheet
Dim line As Integer
endline = Range("A1000").End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Total" Then
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"
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:
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
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
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
Add Missing Leading Zeros To Text
I have a spreadsheet full of site codes which should be the format:
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?
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.
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."
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?