how to delete first 4 characters in a cell?

**sami770** · 05-29-2011, 05:20 AM

Guys,
I have a simple question: I have a single column with text values.
Each cell has 4 non useful characters: 3 numbers and a comma (,) - see file attached
Is there any way to remove this 4 characters in each cell, using a formula or a macro?
Thank you!

**Andy Pope** · 05-29-2011, 05:23 AM

use a helper column, then copy paste values back over original data.

=MID(A1,5,LEN(A1))

**arthurbr** · 05-29-2011, 05:27 AM

A non formula solution

Select data
Menu Data - text to columns
Select delimited - Next
Check commas - Next
Check " do not import column after selecting the first column ( automatic)
Finish

**Andy Pope** · 05-29-2011, 05:29 AM

Note that the text to columns approach will split up some the the records futher down into multiple cells.

**sami770** · 05-29-2011, 05:30 AM

Dear Andy - your formula works like charm - thanks a lot
However, a macro might be more useful for me
I am using the "personal" sheet in the excel startup folder and I am adding all my useful macro's there so that it stay handy
Is there a way to do the same thing using a macro?

Originally Posted by Andy Pope

use a helper column, then copy paste values back over original data.

=MID(A1,5,LEN(A1))

**arthurbr** · 05-29-2011, 05:31 AM

Good point Andy

To avoid this use " fixed width" instead of "delimited"

**Andy Pope** · 05-29-2011, 05:33 AM

Please Login or Register  to view this content.

**sami770** · 05-29-2011, 05:33 AM

Thanks Arthur!
But like I asked Andy: is there a way to do this using a macro?

Originally Posted by arthurbr

Good point Andy

To avoid this use " fixed width" instead of "delimited"

**Andy Pope** · 05-29-2011, 05:37 AM

Actually do you really want to remove the first 4 characters?

What about this record

84,acupuncture Alabama

The returned value would be

cupuncture Alabama

Maybe you want to remove characters up to the first comma?

Please Login or Register  to view this content.

**sami770** · 05-29-2011, 05:42 AM

PERFECT
Andy, I do appreciate this!
Sami

Originally Posted by Andy Pope

Please Login or Register  to view this content.

**martindwilson** · 05-29-2011, 06:21 AM

given your example has only one comma use find replace
find *, replace with nothing

**Andy Pope** · 05-29-2011, 06:44 AM

There are records with multiple commas that will be wrecked.

335,acupuncture+St. Louis,,,, Louis
375,acupuncture+St. Paul,,,, Paul
377,acupuncture+St. Petersburg,,,, Petersburg
490,aircraft +St. Petersburg,,,, Petersburg
491,aircraft +St. Paul,,,, Paul
492,aircraft +St. Louis,,,, Louis
619,acupunctureSt. Petersburg,,,, Petersburg
620,acupunctureSt. Paul,,,, Paul
621,acupunctureSt. Louis,,,, Louis
672,St. Paul,,,, Paul
674,St. Louis,,,, Louis
685,St. Petersburg,,,, Petersburg
823,aircraft St. Louis,,,, Louis
833,aircraft St. Paul,,,, Paul
834,aircraft St. Petersburg,,,, Petersburg

**martindwilson** · 05-29-2011, 07:01 AM

didnt see those lol

how to delete first 4 characters in a cell?

LinkBack

Thread Tools

Rate This Thread

Display

how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Re: how to delete first 4 characters in a cell?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions