PROBLEM: Some (or all) of your entries have a character at the beginning that needs to be removed.
12125551212 is a phone number, but you need to remove the 1 and have 2125551212.
SOLUTION: The trick is to use a combination of functions. I am assuming you already know how to use the IF function. (My favorite and most used function.)
- Make a new column or click into an empty column.
- Insert =IF(LEFT(X#,1)="1",MID(X#,2,255),X#) where X# is the cell location.
- Your function should give you the result your are looking for.
- If you want the value when it is done and not the function which might change, simply copy the cell and go to PASTE SPECIAL... under the EDIT menu.
- Select VALUES instead of ALL under PASTE.
You would obviously want to use your number(s) instead of 1.
WHY IT WORKS: The function is configuring the following with ugly colors to help you understand...
If the first left-most character in cell X# equals 1, copy the contents of cell X#,
start at the second character and paste the next 255 characters of the contents.
Write the new entry.
LEFT(X#,1)="1" translates to Start from the Left (Cell Location,First Character Position)="Character You Want to Remove"
MID(X#,2,255) translates to Extract a Sub-String (Cell Location,New First Character Position,Amount of Characters After New Position)
,X#) translates to Tell Me the New String.
You can do this with a number of characters too.
Where A1 contains Astros the function of =IF(LEFT(A1,3)="Ast",MID(A1,4,255),A1) would return ros.
Where A1 contains Astros the function of =IF(LEFT(A1,3)="Ast",MID(A1,3,3),A1) would return tro.
You can see how this would help you get rid of pesky repetitive words our exports sometimes include. I hope this wasn't too confusing.