how to add a letter to front of column sample ID
 

I have an excel spreadsheet consisting of columns of "patient ID's" and then various columns of "numerical values".

Some patient ID's are "DD-0002-0001PK" as an example. I Want to add a "L" to some of those sample names....like "DD-0002-001PK-L" how can I do that?

I can't just put ="L"&A1, because my values are text and if I do that it just changes it to a 0, and I need those ID labels, but I don't want to have to go back manually and type in "L". Is there anyway that I can put "L" on the back of the first 40 ID's?

how to add a letter to front of column sample ID
 

"msdelila" wrote:
I have an excel spreadsheet consisting of columns of
"patient ID's" and then various columns of "numerical values".

Why should we care about what is in the other columns?

I ask because if that is significant, I am misunderstanding something.


"msdelila" wrote:
Some patient ID's are "DD-0002-0001PK" as an example.
I Want to add a "L" to some of those sample names....like
"DD-0002-001PK-L" how can I do that?

=A1&"-L"

However, that presumes that A1 (patient IDs) are indeed text.

If some of them are entered as numbers formatted to appear in the form
00-0000-00000, A1 will not be interpreted as intended above. In that case,
you might need something like:

=IF(ISNUMBER(A1),TEXT(A1,"00-0000-00000"),A1) & "-L"


"msdelila" wrote:
I can't just put ="L"&A1, because my values are text and
if I do that it just changes it to a 0

Say what? Nonsense!

You cannot do ="L"&A1 because the puts "L" in front, but your example
demonstrates that you want to __append__ "-L" (after the end).

Moreover, ="L"&A1 will never return the number 0 or even the string "0".

One way (the only way I can think of, off-hand) that a cell with that
formula might __display__ "0" is if you have the pretty-silly Custom format
;;;"0" or the equivalent.


"msdelila" wrote:
I need those ID labels, but I don't want to have to go
back manually and type in "L". Is there anyway that I
can put "L" on the back of the first 40 ID's?

Again, your example indicates that you want to append "-L", not simply "L".

If the first 40 IDs are in A1:A40, put =A1&"-L" into B1 and copy down
through B40.

(Or use the IF(ISNUMBER...) alternative, if needed.)

If you want to replace A1:A40 with the values in B1:B40, copy B1:B40, then
use paste-special-value to paste over A1:A40. Now you can remove B1:B40.

If you need to repeat this operation many times over time (e.g. pulling data
from a database), we could provide a macro to make this change. But do you
really want to deal with VBA?

how to add a letter to front of column sample ID
 

On Friday, November 9, 2012 1:09:10 PM UTC-8, msdelila wrote:
I have an excel spreadsheet consisting of columns of "patient ID's" and

then various columns of "numerical values".



Some patient ID's are "DD-0002-0001PK" as an example. I Want to add a

"L" to some of those sample names....like "DD-0002-001PK-L" how can I

do that?



I can't just put ="L"&A1, because my values are text and if I do that it

just changes it to a 0, and I need those ID labels, but I don't want to

have to go back manually and type in "L". Is there anyway that I can

put "L" on the back of the first 40 ID's?









--

msdelila

Hi msdelila,

joeu2004 has given you plenty to chew on and solve your query.

Here is a little snippet of vba code that may be of use if you want to go that way.

I did a drop-down list in F1, perhaps you have a few different letters you may want to choose for various patients.

Click in F1 and select the desired letter to add to the ID's. (Or just enter the letter in F1.)

In the Patient ID's list select all the patients you want to add F1 to (use the ctrl key to select as many as you want).

Run the code. I assigned a short-cut key, ctrl + p, to the macro which you could do also.

Option Explicit

Sub AddToIt()
Dim c As Range
For Each c In Selection
c.Value = c.Value & "-" & Range("F1").Value
Next
End Sub

HTH
Regards,
Howard