Parse delimiter is change from text to numbers
 

I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

Parse delimiter is change from text to numbers
 

Take a look at Text to columns under Data. Use Space as the delimiter.

HTH
Regards,
Howard

"ORLANDO V" wrote in message
...
I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market
value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

Parse delimiter is change from text to numbers
 

One way (since the asset name appears to have space characters - else
Data/Text to Columns, Delimited/Space):

Assuming string in A2 (and country names containing no spaces):

B2: =TRIM(A2)
(Needed only if there may be multiple contiguous space characters in the
string)

C2: =LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-2))-1)

D2: =MID(LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1))-1), LEN(C2)+2, 255)

E2: =--MID(LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))-1), LEN(C2)+LEN(D2)+3, 255)


F2: =--MID(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))+1,255)

In article ,
ORLANDO V wrote:

I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

Parse delimiter is change from text to numbers
 

On Tue, 23 Dec 2008 15:40:57 -0700, JE McGimpsey wrote:

One way (since the asset name appears to have space characters - else
Data/Text to Columns, Delimited/Space):

Assuming string in A2 (and country names containing no spaces):

B2: =TRIM(A2)
(Needed only if there may be multiple contiguous space characters in the
string)

C2: =LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-2))-1)

D2: =MID(LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1))-1), LEN(C2)+2, 255)

E2: =--MID(LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))-1), LEN(C2)+LEN(D2)+3, 255)


F2: =--MID(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))+1,255)

When I try your solution here, it fails with countries that have more than one
name. For example, Great Britain or United States.
--ron

Parse delimiter is change from text to numbers
 

On Tue, 23 Dec 2008 12:58:31 -0800, ORLANDO V
wrote:

I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

The problem is parsing out the country name, since there is no good divider
between the Asset Name and the Country, and it is theoretically possible for an
Asset Name to contain a Country Name. It is also possible for a Country to have
multiple words in the name (e.g. Great Britain; United States; etc).

For Units, you could use:

=--TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),198),99))

and for Market Value:

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

The preceding double unary converts the values into numbers; otherwise they
will be returned as Text.

Because of the problem with parsing out the Country Name, the approach I would
use would be to have a List of Countries, and extract the name that is followed
by the Units and Market Value numbers. This can be done easily using Regular
Expressions, but would require a User Defined Function (meaning VBA).

And if you are doing that, you might as well use the same approach for the
other portions, too.

So, somewhere set up a List of Countries.

You can then use this UDF:

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula in the following format:

=parsespecial(cell_ref, item, country_list)

Item can be "Asset", "Country", "Units", or "Market"

country_list is a range reference where you have stored a list of valid country
names.

==========================================
Option Explicit
Function ParseSpecial(str As String, sItem As String, _
rCountryList As Range) As Variant
Dim re As Object, mc As Object
Dim sPattern As String
Dim CountryList As String
Dim c As Range

For Each c In rCountryList
CountryList = CountryList & c.Text & "|"
Next c
CountryList = "(" & Left(CountryList, Len(CountryList) - 1) & ")"

Select Case Left(UCase(sItem), 5)
Case Is = "COUNT"
sPattern = CountryList & "(?=(\s+[\d.,]+){2}\s*$)"
Case Is = "ASSET"
sPattern = "^.*(?=\s+" & CountryList & "(?=(\s+[\d.,]+){2}\s*$))"
Case Is = "UNITS"
sPattern = "[\d,]+(?=\s+[\d,]+\s*$)"
Case Is = "MARKE"
sPattern = "[\d,]+(?=\s*$)"
Case Else
MsgBox ("sItem must be 'Country', 'Asset', 'Units' or 'Market Value'")
ParseSpecial = CVErr(xlErrNA)
Exit Function
End Select

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(str) = True Then
Set mc = re.Execute(str)
ParseSpecial = mc(0).Value
End If
End Function
=====================================



--ron

Parse delimiter is change from text to numbers
 

Yeah, that's why I wrote:

Assuming string in A2 (and country names containing no spaces)

Can't think of a convenient way to use formulae to parse in that case...

Doesn't mean it can't be done, of course.


In article ,
Ron Rosenfeld wrote:

When I try your solution here, it fails with countries that have more than one
name. For example, Great Britain or United States.
--ron

Parse delimiter is change from text to numbers
 

It appears that your "Asset name" can have spaces in it, and your country
can have spaces in it, and you are using spaces as the delimiter between
items... how would you expect a formula to know when the space is a
separator in the name or a delimiter between them? Two possible ways around
this problem... one, if your "Asset name **always** has one space in it or,
two, if you have a list of all the possible country names in a column
somewhere, then we can work around this problem... otherwise, I don't see
how you can pick out one name from the other.

--
Rick (MVP - Excel)


"ORLANDO V" wrote in message
...
I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market
value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

Parse delimiter is change from text to numbers
 

This worked great. Thank you. (Sorry but for some reason this post did not
show up in my side for a day -that's why I re-posted. Is there a way to see
just my own questions and any responses rather than having to search as I do
now ?)

"JE McGimpsey" wrote:

One way (since the asset name appears to have space characters - else
Data/Text to Columns, Delimited/Space):

Assuming string in A2 (and country names containing no spaces):

B2: =TRIM(A2)
(Needed only if there may be multiple contiguous space characters in the
string)

C2: =LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-2))-1)

D2: =MID(LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1))-1), LEN(C2)+2, 255)

E2: =--MID(LEFT(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))-1), LEN(C2)+LEN(D2)+3, 255)


F2: =--MID(B2,FIND("$",SUBSTITUTE(B2," ", "$",
LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))+1,255)

In article ,
ORLANDO V wrote:

I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

Parse delimiter is change from text to numbers
 

Wow, this looks like a very good solution and it calls to mind a lot of other
applications I will use it for. Thank you.


"Ron Rosenfeld" wrote:

On Tue, 23 Dec 2008 12:58:31 -0800, ORLANDO V
wrote:

I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

The problem is parsing out the country name, since there is no good divider
between the Asset Name and the Country, and it is theoretically possible for an
Asset Name to contain a Country Name. It is also possible for a Country to have
multiple words in the name (e.g. Great Britain; United States; etc).

For Units, you could use:

=--TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),198),99))

and for Market Value:

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

The preceding double unary converts the values into numbers; otherwise they
will be returned as Text.

Because of the problem with parsing out the Country Name, the approach I would
use would be to have a List of Countries, and extract the name that is followed
by the Units and Market Value numbers. This can be done easily using Regular
Expressions, but would require a User Defined Function (meaning VBA).

And if you are doing that, you might as well use the same approach for the
other portions, too.

So, somewhere set up a List of Countries.

You can then use this UDF:

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula in the following format:

=parsespecial(cell_ref, item, country_list)

Item can be "Asset", "Country", "Units", or "Market"

country_list is a range reference where you have stored a list of valid country
names.

==========================================
Option Explicit
Function ParseSpecial(str As String, sItem As String, _
rCountryList As Range) As Variant
Dim re As Object, mc As Object
Dim sPattern As String
Dim CountryList As String
Dim c As Range

For Each c In rCountryList
CountryList = CountryList & c.Text & "|"
Next c
CountryList = "(" & Left(CountryList, Len(CountryList) - 1) & ")"

Select Case Left(UCase(sItem), 5)
Case Is = "COUNT"
sPattern = CountryList & "(?=(\s+[\d.,]+){2}\s*$)"
Case Is = "ASSET"
sPattern = "^.*(?=\s+" & CountryList & "(?=(\s+[\d.,]+){2}\s*$))"
Case Is = "UNITS"
sPattern = "[\d,]+(?=\s+[\d,]+\s*$)"
Case Is = "MARKE"
sPattern = "[\d,]+(?=\s*$)"
Case Else
MsgBox ("sItem must be 'Country', 'Asset', 'Units' or 'Market Value'")
ParseSpecial = CVErr(xlErrNA)
Exit Function
End Select

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(str) = True Then
Set mc = re.Execute(str)
ParseSpecial = mc(0).Value
End If
End Function
=====================================



--ron

Parse delimiter is change from text to numbers
 

Thank you! I believe Ron R. came to same conclusion and composed workaround
you suggest. Thank you.


"Rick Rothstein" wrote:

It appears that your "Asset name" can have spaces in it, and your country
can have spaces in it, and you are using spaces as the delimiter between
items... how would you expect a formula to know when the space is a
separator in the name or a delimiter between them? Two possible ways around
this problem... one, if your "Asset name **always** has one space in it or,
two, if you have a list of all the possible country names in a column
somewhere, then we can work around this problem... otherwise, I don't see
how you can pick out one name from the other.

--
Rick (MVP - Excel)


"ORLANDO V" wrote in message
...
I have a long list of items like this one below.
They consist of four things: Asset name, country, units, and market
value.

Accor SA France 3,300,000 177,214,917

I want to parse this? Any ideas on the best way to do this? Open to all
suggestions. Prefer formulas over VBA for this one .

Parse delimiter is change from text to numbers
 

It really depends on how you access these newsgroups. My news reader
flags threads that I have posted in automatically.

In article ,
ORLANDO VAZQUEZ wrote:

Is there a way to see just my own questions and any responses rather
than having to search as I do now ?

Parse delimiter is change from text to numbers
 

On Fri, 2 Jan 2009 10:04:01 -0800, ORLANDO VAZQUEZ
wrote:

Wow, this looks like a very good solution and it calls to mind a lot of other
applications I will use it for. Thank you.


Glad to help. Thanks for the feedback.
--ron