Parse variable length strings
 

Hi -

I have a data-dump that's used as the source data for a variance
report. One of the fields contains a transaction date and the name of
the purchaser ... this entry is always formatted like this:
dd.mm.yyyy lastname,firstname

Of course, the first & last names are of variable lengths. Is it
possible to build a formula that would essentially strip off the date
and space (eg "05.15.2009 "), leaving me with the full name of the
purchaser?

My current solution utilizes the text-to-columns functionality, but
requires the user to add a column (in the correct location or risk
over-writing data) and then using text-to-columns ... I'd like it to
be an 'automatic' action when the data is refreshed (ie, re-pasted).

Any ideas?

Thanks, ray

Parse variable length strings
 

Give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"Ray" wrote in message
...
Hi -

I have a data-dump that's used as the source data for a variance
report. One of the fields contains a transaction date and the name of
the purchaser ... this entry is always formatted like this:
dd.mm.yyyy lastname,firstname

Of course, the first & last names are of variable lengths. Is it
possible to build a formula that would essentially strip off the date
and space (eg "05.15.2009 "), leaving me with the full name of the
purchaser?

My current solution utilizes the text-to-columns functionality, but
requires the user to add a column (in the correct location or risk
over-writing data) and then using text-to-columns ... I'd like it to
be an 'automatic' action when the data is refreshed (ie, re-pasted).

Any ideas?

Thanks, ray

Parse variable length strings
 

this entry is always formatted like this:
dd.mm.yyyy lastname,firstname

Try this:

=MID(A1,13,255)

--
Biff
Microsoft Excel MVP


"Ray" wrote in message
...
Hi -

I have a data-dump that's used as the source data for a variance
report. One of the fields contains a transaction date and the name of
the purchaser ... this entry is always formatted like this:
dd.mm.yyyy lastname,firstname

Of course, the first & last names are of variable lengths. Is it
possible to build a formula that would essentially strip off the date
and space (eg "05.15.2009 "), leaving me with the full name of the
purchaser?

My current solution utilizes the text-to-columns functionality, but
requires the user to add a column (in the correct location or risk
over-writing data) and then using text-to-columns ... I'd like it to
be an 'automatic' action when the data is refreshed (ie, re-pasted).

Any ideas?

Thanks, ray

Parse variable length strings
 

Good point... the 'dd' part didn't register with me.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
this entry is always formatted like this:
dd.mm.yyyy lastname,firstname

Try this:

=MID(A1,13,255)

--
Biff
Microsoft Excel MVP


"Ray" wrote in message
...
Hi -

I have a data-dump that's used as the source data for a variance
report. One of the fields contains a transaction date and the name of
the purchaser ... this entry is always formatted like this:
dd.mm.yyyy lastname,firstname

Of course, the first & last names are of variable lengths. Is it
possible to build a formula that would essentially strip off the date
and space (eg "05.15.2009 "), leaving me with the full name of the
purchaser?

My current solution utilizes the text-to-columns functionality, but
requires the user to add a column (in the correct location or risk
over-writing data) and then using text-to-columns ... I'd like it to
be an 'automatic' action when the data is refreshed (ie, re-pasted).

Any ideas?

Thanks, ray