MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting the first 14 characters in a cell


Posted by Stuart on February 06, 2002 6:34 PM

How can I delete the first 14 characters in a cell?

Thnx
Stuart


Posted by Jacob on February 06, 2002 6:40 PM

Hi

Lets say data is in A1 then

=Mid(A1,15,1000)

This will give only the text from char 15 on.

HTH

Jacob

Posted by Richard S on February 06, 2002 6:53 PM

That will give you the next 1000 chars. If there is more than 1000, it won't work.
=RIGHT(A74,LEN(A74)-14)
Will get any number. However, if cell A1 has less than 14 chars, will return an error.
Richard

Posted by Greg on February 06, 2002 7:26 PM

To do this I typically use a combination of functions...

Assume the numbers are in column B. In column C I enter the function =RIGHT(B1,LEN(B1)-14)

This function finds the length of the characters in the cell, subtracts 14 and returns the right most remainder.

Copy the function down column C for all the values in column B.

You can then copy column C and do a paste special and select the values option. This converts the formula to text. It also means that you can then delete column B without having any impact on column C.

Good luck
Greg How can I delete the first 14 characters in a cell?

Posted by Greg on February 06, 2002 7:55 PM

To avoid the error problem, a variation on this solution would be;
=IF(ISERROR(RIGHT(B10,LEN(B10)-14)),B10,RIGHT(B10,LEN(B10)-14))

That is, if it is an error, use the existing number otherwise perform the calculation to return the right most 14 characters.

Cheers
Greg


Posted by Mike on February 07, 2002 5:15 AM

Another option based on Jacob's original solution is to use:
=IF(LEN(A1)<15,A1,MID(A1,15,LEN(A1)))
This will output A1 value if the length is less than 15 characters, and will output the edited version of A1 if over 14. By using the LEN(A1) as the last character position any lenth of word is catered for.
If the required output is a blank cell when A1 contains <15 characters then all that is needed is: =MID(A1,15,LEN(A1))

HTH

Mike To avoid the error problem, a variation on this solution would be;


Posted by IML on February 07, 2002 6:11 AM

another read

would be to use
=RIGHT(A1,(LEN(A1)-14)*(LEN(A1)>14))
which returns a nothing if a cell is a length of 14 or under. Another option based on Jacob's original solution is to use:


Posted by Chris D on February 07, 2002 12:09 PM

guys... would =REPLACE(A1,1,14,"") do the trick ?