Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating Question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating Question
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. "amik" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating (?) question | Excel Discussion (Misc queries) | |||
Formating Question | Excel Worksheet Functions | |||
Conditinal Formating question | Excel Discussion (Misc queries) | |||
Formating question | Excel Discussion (Misc queries) | |||
Formating Question Please | Excel Discussion (Misc queries) |