Ignore first 6 characters

G

Gordon Forbes

Hi there,

I have a a column with entries of various lengths but want
to ignore the first 6 characters i.e.

158413BSS PTS would be BSS PTS
141288STI would be STI
141366A would be A

is this possible?

Many thanks
Gordon
 
A

Andy B

Hi

You could use a helper column alongside your data with this in:
=MID(A2,7,999)
Fill this down and once you are happy you can copy the range and Paste
Special . . Values to fix the values in place. You can then delete the
original data (if required)
 
G

Gordon Forbes

Thanks for the prompt reply Norman,

I had been trying for ages with LEFT and RIGHT functions
then actually worked it out myself using the MID function
that Andy B also suggested.

Cheers
 
G

Gordon Forbes

Hi Andy,

thanks for the prompt reply, I had been trying LEFT and
RIGHT ut then found the MID as you suggested below, works
a treat.

Cheers
 
E

Earl Kiosterud

Norman,

The OP doesn't say if A1 could ever be less than 6 characters, but the
formula will give an error in that case. Maybe use:
=RIGHT(A1,MAX(0,LEN(A1)-6)).
Actually, that MID works pretty well! :)
 
N

Norman Harker

Hi Earl!

Big mistake!

Rule 2. Never take OP at their word.

I'm having difficulty (laughing with a toothache!) but =MID(A3,7,999)
fails if text is over 1005 characters long so I suppose I should use:

=MID(A3,7,32760)
 
D

Dave Peterson

You may want to try Data|Text to columns.

You can say that your data is fixed width and draw a line between character 6
and 7. (and remove any that excel guessed at)

Then skip that first field.

Put it in a different column or right over the original data.
 
E

Earl Kiosterud

Norman,

Yeah. Harald used 255. What does he know that we don't?
You can use:
MID(A3, 7, LEN(A3))
or slightly more correctly:
MID(A3, 7, LEN(A3)-6)
but that fails on an empty cell with @#$&^*&#@^$*&VALUE!!!!!!! or some such
Excel-cursing.
So you need:
=MID(A7,7,MAX(0,LEN(A7)-6))
But by now we've forgotten what we set out to do, and it ain't all that
apparent looking at that formula! :)
And I don't know what maximums we may still have exceeded. As King Pelinore
said to Arthur, "IT'S BLOODY ENDLESS, ISN'T IT?"

Sorry about the toothache. No fun.
 
Top