Parse data from string
okrob
http://groups.google.com/group/microsoft.public.excel.worksheetfunctions/browse_frm/thread/8c6541d562c3db71/66953084d4a98d93?hl=en#66953084d4a98d93
The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob
Tom Ogilvy
--
Regards,
Tom Ogilvy
"okrob" <merobwh...@yahoo.com> wrote in message
news:1169402854....@m58g2000cwm.googlegroups.com...
Ron Rosenfeld
Download and install Longre's free morefunc.xll add-in (easily distributed with
the workbook if that is an issue) from http://xcell05.free.fr
Then use this formula:
=REGEX.SUBSTITUTE(A1,"(\w+)\s+(\w+).*","[2], [1]")
--ron
okrob
Changed the last &MID(A1,2,1) to &MID(A1,2,2) to get both initials, but
when I do that, if there's only one initial present, I get the space
before the last name...
The cell looks like this when there is only one initial present:
[ J KINGSLEY S444 1000R 1500 XXXX ] <== two spaces
between initials and the last name...
Again, with both initials, one space...
[ JB KINGSLEY S444 1000R 1500 XXXX ]
It's just how the data comes into excel from a terminal emulator screen
copy.
Rob
okrob
This worked great! Looks like I added a whole host of functions with
this addin...
Best part about it is that it's distributable.
Rob
Tom Ogilvy
=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&Trim(MID(A1,2,2))
--
regards,
Tom Ogilvy
"okrob" <merobwh...@yahoo.com> wrote in message
news:1169407544....@s34g2000cwa.googlegroups.com...
okrob
That works too. I have a decision to make as to which solution to use.
Yours may suit me better in that I won't have to worry about making
sure there is access to the addin that Ron suggested. The spreadsheet
will be viewed on thin clients (only excel reader) across many
facilities.
Rob
Ron Rosenfeld
>Thanks Ron...
>This worked great! Looks like I added a whole host of functions with
>this addin...
>Best part about it is that it's distributable.
>Rob
You're welcome. Thanks for the feedback.
Yes there are a number of useful functions. And they can also be used from
within VBA.
About the only downside I've found is that inputs are limited to 255
characters.
--ron
okrob
My final solution, if you're interested...
I needed to get rid of the space before the last name only if there
wasn't a middle initial, so I checked for middle initial and if true, I
worked one solution, if not, I worked the other...
=IF(MID(A28,3,1)=" ",(MID(A28,FIND(" ",A28,4)+1,FIND(" ",A28,5)-FIND("
",A28,2)-1)&", "&MID(A28,2,1)),
(MID(A28,FIND(" ",A28,2)+1,FIND(" ",A28,5)-FIND(" ",A28,2)-1)&",
"&MID(A28,2,2)))
Works perfectly!
Rob
Ron Rosenfeld
wrote:
I noted in a previous posting that you wanted to get rid of the initial space
if there was not middle initial.
You could modify the function I posted as follows:
=REGEX.SUBSTITUTE(A1,"\s*(\w+)\s+(\w+).*",
"=if(len(""[1]"")=2,"" [2], [1]"",""[2], [1]"")" )
--ron
okrob
Thanks again,
Rob