Parse data from string
 

Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob

Parse data from string
 

=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&", "&MID(A1,2,1)

--
Regards,
Tom Ogilvy


"okrob" wrote in message
ps.com...
Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob

Parse data from string
 

On 21 Jan 2007 10:07:34 -0800, "okrob" wrote:

Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob


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

Parse data from string
 

Thanks Tom
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



Tom Ogilvy wrote:
=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&", "&MID(A1,2,1)

--
Regards,
Tom Ogilvy


"okrob" wrote in message
ps.com...
Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob

Parse data from string
 

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




Ron Rosenfeld wrote:
On 21 Jan 2007 10:07:34 -0800, "okrob" wrote:

Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob


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

Parse data from string
 

I thought you said you only wanted the first initial. So you can do

=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&Trim(MID(A1,2,2))


--
regards,
Tom Ogilvy

"okrob" wrote in message
ups.com...
Thanks Tom
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



Tom Ogilvy wrote:
=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&MID(A1,2,1)

--
Regards,
Tom Ogilvy


"okrob" wrote in message
ps.com...
Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob

Parse data from string
 

On 21 Jan 2007 11:34:10 -0800, "okrob" wrote:

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

Parse data from string
 

Thanks again Tom...
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



Tom Ogilvy wrote:
I thought you said you only wanted the first initial. So you can do

=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&Trim(MID(A1,2,2))


--
regards,
Tom Ogilvy

"okrob" wrote in message
ups.com...
Thanks Tom
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



Tom Ogilvy wrote:
=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&MID(A1,2,1)

--
Regards,
Tom Ogilvy


"okrob" wrote in message
ps.com...
Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob

Parse data from string
 

Tom,

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


Tom Ogilvy wrote:
I thought you said you only wanted the first initial. So you can do

=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&Trim(MID(A1,2,2))


--
regards,
Tom Ogilvy

"okrob" wrote in message
ups.com...
Thanks Tom
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



Tom Ogilvy wrote:
=MID(A1,FIND(" ",A1,2)+1,FIND(" ",A1,5)-FIND(" ",A1,2)-1)&",
"&MID(A1,2,1)

--
Regards,
Tom Ogilvy


"okrob" wrote in message
ps.com...
Can someone take a look at this post:
http://groups.google.com/group/micro...953084d4a98d93

The group isn't very active today and I need help as soon as
possible...
Thanks,
Rob

Parse data from string
 

On Sun, 21 Jan 2007 14:54:48 -0500, Ron Rosenfeld
wrote:

On 21 Jan 2007 11:34:10 -0800, "okrob" wrote:

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

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

Parse data from string
 

I did finally figure that out too after playing with the function.
Thanks again,
Rob


Ron Rosenfeld wrote:
On Sun, 21 Jan 2007 14:54:48 -0500, Ron Rosenfeld
wrote:

On 21 Jan 2007 11:34:10 -0800, "okrob" wrote:

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

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