Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I eliminate spaces/characters from the beginning of a cell
I have a macro that has hit a snag. On one of the pages of the workbook, I
have a list of product codes. Some are copied over and sort fine. Others, the same codes, are copied with a leading space or a " ' " before the code. Is there an If then statement I can use that will remove any leading spaces or this symbol? It's messing up a sort that the entire macro depends on. Not all of the product codes have these spaces or characters which is why I need an if then statement to do nothing if they aren't there and to remove them if they are. Thanks in advance for any help you can offer. -- A Waller |
#2
|
|||
|
|||
Hi!
Try to identify what these symbols are. A1 = product code with a leading "space" or " ' " B1 = CODE(A1) B1 will return the char code for the first char in cell A1. Once you have them identified then you can use EditReplace to remove them. For example, the code formula returns 160 which is the char code for a non-breaking space. Select the range of product codes then goto EditReplace In Find What: hold down the ALT key and use the numeric keypad and enter: 0160. When you release the ALT key, you'll see what appears to be a space in the Find what box. How do you see a space? The cursor moves to the right! Now, in the Replace With box, leave this empty. don't enter anything. Then click Replace All. So, what you're doing is replacing all the char 0160's in the range with nothing. I have to do this almost every time I copy data posted in these newsgroups and then paste it into a worksheet to work on a solution to a question. Do the same thing for the " ' " char. Biff "A Waller" wrote in message ... I have a macro that has hit a snag. On one of the pages of the workbook, I have a list of product codes. Some are copied over and sort fine. Others, the same codes, are copied with a leading space or a " ' " before the code. Is there an If then statement I can use that will remove any leading spaces or this symbol? It's messing up a sort that the entire macro depends on. Not all of the product codes have these spaces or characters which is why I need an if then statement to do nothing if they aren't there and to remove them if they are. Thanks in advance for any help you can offer. -- A Waller |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I eliminate 1 bar on the bar chart? | Charts and Charting in Excel | |||
how eliminate zeros in blank cells using paste link | Excel Worksheet Functions | |||
How do I eliminate zeros from my Excel database? | Excel Discussion (Misc queries) | |||
macro to eliminate spaces between words | Excel Discussion (Misc queries) | |||
eliminate white space in Excel file | Excel Discussion (Misc queries) |