Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the DEFAULT delimiter for pasting text data? | Excel Discussion (Misc queries) | |||
Parse text & numbers & format | Excel Worksheet Functions | |||
Parse text & numbers & format | Excel Worksheet Functions | |||
Parse text & numbers & format | Excel Worksheet Functions | |||
Parse text & Numbers | Excel Worksheet Functions |