How do I parse columns?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChuckNC - 22 May 2006 22:06 GMT

it doesn't appear to be a simple text to columns function. here's what i want
to do:

i have cell that contains :02110610 and I want to reduce it to 010. is there
a formula, or the like, that will strip out the the first four characters
(:021) and the last three characters (610) and then add a zero, so i end up
with 010? can i do this in a column on one row and then drag it down +/- 2300
rows of data? thanks a multitude, in advance, for your help, folks.
-chuck

p.s. pls forgive my ignorance of the proper language for this stuff.

Reply to this Message

Marcelo - 22 May 2006 22:14 GMT

Chuck,

try

="0"&mid(a3,5,2)

hope its help

Marcelo from Brazil

> it doesn't appear to be a simple text to columns function. here's what i want
> to do:
[quoted text clipped - 7 lines]
>
> p.s. pls forgive my ignorance of the proper language for this stuff.

Reply to this Message

ChuckNC - 22 May 2006 22:47 GMT

brilliant! thank you, marcelo!

> Chuck,
>
[quoted text clipped - 17 lines]
> >
> > p.s. pls forgive my ignorance of the proper language for this stuff.

Reply to this Message

David Biddulph - 22 May 2006 22:14 GMT

> it doesn't appear to be a simple text to columns function. here's what i
> want
[quoted text clipped - 11 lines]
>
> p.s. pls forgive my ignorance of the proper language for this stuff.

=0&MID(A1,5,LEN(A1)-7) and copy this down the other rows.

Signature

David Biddulph

Reply to this Message

ChuckNC - 25 May 2006 15:30 GMT

Thanks David. Brilliant, too!

> > it doesn't appear to be a simple text to columns function. here's what i
> > want
[quoted text clipped - 13 lines]
>
> =0&MID(A1,5,LEN(A1)-7) and copy this down the other rows.

Reply to this Message