Rewrite name in new format

[ALaw]

Hi there,

I have a column with numbers such as
100/16-14-059-08W6/3
100/02-16-059-08W6/3
100/15-17-059-08W6/0
100/02-18-059-08W6/2

I need these to be rewritten in a column in the following format
100161405908W6003
100021605908W6003
100151705908W6000
100021805908W6002

So basically all dashes and slashes are removed, and there is a 00 added
infront of the last number.

I don't want to type it in since I have a lot of numbers, how can I do this
in excel?

 

[dlw]

search and replace (with nothing) to get rid of all slashes and dashes
then =left(a1,14)&"000"&right(a1,1) to get the zeros in.
that's assuming they are all the same length, if not, replace the 14 with
=len(a1)-1

 

[Ardus Petus]

=MID(A1,1,3)&MID(A1,5,2)&MID(A1,8,2)&MID(A1,11,3)&MID(A1,15,4)&"00"&MID(A1,20,1)

HTH

 

[FSt1]

hi
if all the number are the same format as your example, you can use this
formula
=LEFT(K2,3)
&MID(K2,5,2)&MID(K2,8,2)&MID(K2,11,3)&MID(K2,15,4)&"00"&RIGHT(K2,1)

regards
FSt1

 

[Pete_UK]

You could do it using Find & Replace (CTRL-H). Highlight the column
containing your data and do CTRL-H (or Edit | Replace):

Find What: 100/
Replace with: 100
click Replace All

CTRL-H again:
Find What: W6/
Replace with: W600
click Replace All


CTRL-H again:
Find What: -
Replace with: leave blank
click Replace All

Hope this helps.

Pete