| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Parse this string | Excel Discussion (Misc queries) | |||
| Parse data where break is a first uppercase character in a string? | Excel Worksheet Functions | |||
| Parse string | Excel Programming | |||
| Parse String | Excel Programming | |||
| Wish to parse through a text string to find data | Excel Programming | |||
Linear Mode
