scan from string

[jyang72211]

Hi all,

I am trying to use the scan from string function for the string below.  I want to try tro commad as the delimiter and ignore the space.  I read the the scan from string function doesn't like space in the scan string.  I tried to get around it but I can't.  If I still want to use that function, how do I get aroun it?  Thanks!

Yik

RD-33-231 ,300274 ,         ,07.08.03

[dlanger]

Can you post a screenshot of your code? Into what part of the string are you interested in and into what data type do you want to format into?

[ben64]

You can use the Search and Replace function to remove the spaces before using the scan from string function.

Ben64

[smercurio_fc]

Try this:

You could also just use the Spreadsheet String to Array to get your self an array of 4 elements.

[jyang72211]

Hi all,

I looked at the input string in code view, and I realized that the space that I saw on normal display is actually not space, but they are \00.  Also, I need to parse out 3 items from the string, and they are RD-33-231, 300274, 07.08.03.  I want them all to be in string.

RD-33-231\00,300274\00\00,\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00,07.08.03

[jcarmody]

Use a Match Pattern to find the comma and strip the whitespace.  You can wire the output of the Trim Whitespace to an indexing tunnel to build your array, but I'm using the variant to simplify cleaning out the instances of no data (between the second an third comma in your sample).  Using variant attributes this way will also keep the loop from recording duplicate entries, so don't use it if you need to capture repeated data.

[ben64 · Accepted solution]

Alternate method.

[jyang72211]

Hi Ben,

Can you explain \00*,\00*,*?

I understand how that work for what is after 300274.  However, it seems like a single /00, was matched and replaced by the comma as well.  How did that happen?

Yik

[jcarmody]

I'll let Ben give the explanation, but I'll provide a hint. 

The asterisk, in a Regular Expression, doesn't mean the same thing it does elswhere.  It's not a "wildcard" like in a Windows search, rather it "[r]epeats the previous item zero or more times. Greedy, so as many items as possible will be matched before trying permutations with [fewer] matches of the preceding item, up to the point where the preceding item is not matched at all." (from here)

[ben64]

Well after tought the first \00* should be replaced by \00+ to avoid the useless situation of replacing a comma by a comma. So I will explain \00+,\00*,*

\00+, will match one or more \00 character followed by a comma (in RD-33-231\00,300274\00\00,\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00,07.08.03 it will match the first 2 red highlighted sequences and the green highlighted sequence).

If you only use this expression it will result in the following string: RD-33-231,300274,,07.08.03 after the search and replace function.

The second part of the regex is use to avoid the double comma in the resulting string \00*,* match 0 or more \00 followed by 0 or more comma. Adding this will make the second red sequence and the green sequence a single match.

\00+,\00*,* will match the 2 following sequences: RD-33-231\00,300274\00\00,\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00\00,07.08.03

It will result in the following string: RD-33-231,300274,07.08.03 after the search and replace function.

Ben64