Formating Question

[amik]

I have a long lists for of stores that contain texta and a number. I am
doing a Vlookup for this column but the formating is wrong. Here are
examples of what the two different texts look like. WalMart #01323 and
WALMART #001323 CARBONDALE (77757) Carbondale, IL. Is there a way to format
all of my cells in the second column to look like WalMart #01323? They arein
order but I can't drap because some of the numbers dont exsist.


Any help would be great

Adam

 

[JLatham]

I'm not sure I understand completely.

If the entries are like "WALMART #001323" and "WALMART #01323", and you want
them to all only have the 5 numbers, then try this:

On that sheet, in an unused column, put a formula like this on the same row
(assumes row 2 and original entries in column A) with the first entry:
=LEFT(A2,FIND("#",A2)) & RIGHT(A2,5)
and fill it down. All entries would be reduced to "WALMART #01323"-like
entries.

Next select all of those cells and use Edit --> Copy. Then choose all the
cells with the original entries in them and use Edit --> Paste Special and
choose the "Values" option. The original entries will be overwritten with
the new ones.


But if you have both types of entries in the column, then a really ugly
formula is needed:
=LEFT(LEFT(A2 & " ",FIND(" ",A2 & " ",FIND(" ",A2 & "
")+1)-1),FIND("#",LEFT(A2 & " ",FIND(" ",A2 & " ",FIND(" ",A2 & " ")+1)-1)))
& RIGHT(LEFT(A2 & " ",FIND(" ",A2 & " ",FIND(" ",A2 & " ")+1)-1),5)

Do the same thing with it. That will take entries like
WALMART #01323
WALMART #001323
and
WALMART #001323 CARBONDALE (77757) Carbondale, IL.
and reduce them to "WALMART #01323"
It does depend on their being a # symbol in the original entry and at least
5 characters after that before a space appears. For entries that are exactly
like "WALMART #01323" it does add the needed space after the 5 digits so that
it works properly.