Remove First Character in a Cell

PROBLEM: Some (or all) of your entries have a character at the beginning that needs to be removed.

For Example:

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.


Facebooktwittergoogle_pluspinterest