Home |
Search |
Today's Posts |
#1
|
|||
|
|||
label problem
I am attempting to take a label with numbers and dashes and remove the
dashes, so I can convert the labels to the same format as my source file in a VLOOKUP. I'm using the following formula: =CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes 0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP doesn't work until I value the formula, then hit F2 and F9 to convert it to a label. The problem is the ' symbol, which shows in the cell until I hit F2 & F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE converts the 0168304007 to a value and removes the leading zero. My source file is a label that contains a leading zero. Is there a better way to do this conversion? If not, is there a way to change the result to a label without manually hitting F2 & F9. I'm converting a few thousand labels, so doing this manually would be a nightmare. |
#3
|
|||
|
|||
Hi
try the formula =TEXT(SUBSTITUTE(A1,"-",""),"0000000000") -- Regards Frank Kabel Frankfurt, Germany "Raven Maniac" <Raven schrieb im Newsbeitrag ... I am attempting to take a label with numbers and dashes and remove the dashes, so I can convert the labels to the same format as my source file in a VLOOKUP. I'm using the following formula: =CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes 0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP doesn't work until I value the formula, then hit F2 and F9 to convert it to a label. The problem is the ' symbol, which shows in the cell until I hit F2 & F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE converts the 0168304007 to a value and removes the leading zero. My source file is a label that contains a leading zero. Is there a better way to do this conversion? If not, is there a way to change the result to a label without manually hitting F2 & F9. I'm converting a few thousand labels, so doing this manually would be a nightmare. |
#4
|
|||
|
|||
Hi Raven
try =TEXT(CONCATENATE(LEFT(D11,1),MID(D11,3,5),MID(D11 ,9,3),RIGHT(D11,1)),"0000000000") (all on one line) - note i had to change your formula a bit to get it to produce the result in your example. Cheers JulieD "Raven Maniac" <Raven wrote in message ... I am attempting to take a label with numbers and dashes and remove the dashes, so I can convert the labels to the same format as my source file in a VLOOKUP. I'm using the following formula: =CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes 0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP doesn't work until I value the formula, then hit F2 and F9 to convert it to a label. The problem is the ' symbol, which shows in the cell until I hit F2 & F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE converts the 0168304007 to a value and removes the leading zero. My source file is a label that contains a leading zero. Is there a better way to do this conversion? If not, is there a way to change the result to a label without manually hitting F2 & F9. I'm converting a few thousand labels, so doing this manually would be a nightmare. |
#5
|
|||
|
|||
In my case, I use the formula that matches DMcRitchie's, because my label is
not set to exactly 10 characters, as some of the other examples would tend to assume. e.g. 0-16830-400-8, 0-16830-400-9,0-16830-400-10. The other formulas would format the last entry to exactly 10 characters. TJP "Raven Maniac" <Raven wrote in message ... I am attempting to take a label with numbers and dashes and remove the dashes, so I can convert the labels to the same format as my source file in a VLOOKUP. I'm using the following formula: =CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes 0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP doesn't work until I value the formula, then hit F2 and F9 to convert it to a label. The problem is the ' symbol, which shows in the cell until I hit F2 & F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE converts the 0168304007 to a value and removes the leading zero. My source file is a label that contains a leading zero. Is there a better way to do this conversion? If not, is there a way to change the result to a label without manually hitting F2 & F9. I'm converting a few thousand labels, so doing this manually would be a nightmare. |
#6
|
|||
|
|||
Thanks everyone for the help. My label will always be 10 characters (product
ISBN), so the "0000000000" works great. TJ "Troy Peterson" wrote: In my case, I use the formula that matches DMcRitchie's, because my label is not set to exactly 10 characters, as some of the other examples would tend to assume. e.g. 0-16830-400-8, 0-16830-400-9,0-16830-400-10. The other formulas would format the last entry to exactly 10 characters. TJP "Raven Maniac" <Raven wrote in message ... I am attempting to take a label with numbers and dashes and remove the dashes, so I can convert the labels to the same format as my source file in a VLOOKUP. I'm using the following formula: =CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes 0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP doesn't work until I value the formula, then hit F2 and F9 to convert it to a label. The problem is the ' symbol, which shows in the cell until I hit F2 & F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE converts the 0168304007 to a value and removes the leading zero. My source file is a label that contains a leading zero. Is there a better way to do this conversion? If not, is there a way to change the result to a label without manually hitting F2 & F9. I'm converting a few thousand labels, so doing this manually would be a nightmare. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Scatter Graph - Data Label Problems | Charts and Charting in Excel | |||
Excel graph y axis label cut | Charts and Charting in Excel | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
how to remove label formatting (eg label to number) | Excel Worksheet Functions |