If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|