Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
String parsing with variable lenght strings | Excel Worksheet Functions | |||
Converting variable text strings to numeric | Excel Discussion (Misc queries) |