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.

A1: '0-16830-400-7
B1: =SUBSTITUTE(A1,"-","")
and the result in B1 will be text

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"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.

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.

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.

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.

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.