Solved

Excel prepend character

Posted on 2013-12-04
6
270 Views
Last Modified: 2013-12-04
I have a column of data and would like to prepend the first character with a special character like # or $. Is there any automated way to do this, thanks

column1
data1
data2
data3
data4

I would like for this to become without going to each row.

#data1
#data2
#data3
0
Comment
Question by:techdrive
6 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
Comment Utility
Formula way:
Create a new column with the formula ="#" & A1
Extend the formula down by a copy paste
Copy the result then paste values over your initial data
Remove the temporary column

Macro way: Select your cells then run the attached code

sub AddLetters
dim cl as range
for each cl in selection.cells
    cl="#"&cl.value
next cl
end sub

Open in new window


Thomas
0
 
LVL 19

Expert Comment

by:jss1199
Comment Utility
Using macro...
1. Select the range that you want to insert the prefix or suffix.
2. Click Developer > Visual Basic, click Insert > Module, and then input the following code:
Sub AddPrefix()
'Updateby20131204
Dim Rng As Range
Dim WorkRng As Range
Dim addStr As String
On Error Resume Next
xTitleId = "AddPrefixToCellRange"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
addStr = Application.InputBox("Add text", xTitleId, "", Type:=2)
For Each Rng In WorkRng
    Rng.Value = addStr & Rng.Value
Next
End Sub

Open in new window

3. Run the macro AddPrefix and enter the range that you want to insert the prefix and then enter the character(s) to add - this can be used for your current situation and others that may require different prefixes without modifying the macro
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
No need VBA

Just type

="#"&A1

then, copy and paste value
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 19

Expert Comment

by:jss1199
Comment Utility
agreed, andrew but the OP did not want to touch each row...
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
All of the above will work but if this is a one off and the 'data' part is common you can just use find/replace - find 'data', replace with '#data'

Reg
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
OP won't have to touch each row but will only need to insert formula in first row and then fill down, using either Ctrl + D or double click the fill handle.

Then copy paste special.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now