Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a letter in front of number in every cell? | Excel Worksheet Functions | |||
I WANT TO INSERT A LETTER IN FRONT OF A NUMBERS | Excel Worksheet Functions | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
how to add a letter in front of each number in a cell | Excel Discussion (Misc queries) |