Parsing / seperating text string in excel cell

[Guest]

Below is an example of a strring of data that i receive. Many times the data
string is over 255 characters in length. ( bigger that the capacity of the
location that i am copying it to.

Is there a way to programatically parse out those strings longer than
allowed in my field

I need to do that to a text string that is both comma deliminated ( item &
quatity) and semicolon deliminated (Record)

262662,1;287740,1;266043,1;265832,1;283351,1;298864,1;292744,1;301754,1;302201,1;283346,1;296536,1;292910,1;274865,1;293748,1;77029,1;47952,1;295392,1;292327,1;273352,1;276065,1;256396,1;269669,1;269671,1;82205,1;301349,1;269668,1;260051,1;81462,1;269670,1;302623,1;281220,1;297459,1;257806,1;271241,1

The item is not always the sam# of characters and the quantiy either

Is there a way to take all text including Say (10) semicolons

 

[Guest]

hi
data>text to columns>delimited>check comma AND semicolon

I just copied your sample data and did the above and it parsed it all out.
excel see it as number after it's parsed. when i pasted it into excel, i
picked up a
merged cell. Not sure it that is a problem for you.
Regards
FSt1

 

[Guest]

Hi,

Not sure if this will help but have a look at "text to columns" under the
Data menu.

Regards!
Jean-Guy

 

[Ron Rosenfeld]

Below is an example of a strring of data that i receive. Many times the data
string is over 255 characters in length. ( bigger that the capacity of the
location that i am copying it to.

Is there a way to programatically parse out those strings longer than
allowed in my field

I need to do that to a text string that is both comma deliminated ( item &
quatity) and semicolon deliminated (Record)

262662,1;287740,1;266043,1;265832,1;283351,1;298864,1;292744,1;301754,1;302201,1;283346,1;296536,1;292910,1;274865,1;293748,1;77029,1;47952,1;295392,1;292327,1;273352,1;276065,1;256396,1;269669,1;269671,1;82205,1;301349,1;269668,1;260051,1;81462,1;269670,1;302623,1;281220,1;297459,1;257806,1;271241,1

The item is not always the sam# of characters and the quantiy either

Is there a way to take all text including Say (10) semicolons

That is certainly possible:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,";",CHAR(1),10))-1)

will give the leftmost characters up to but not including the 10th semicolon.

But is that what you really want?

You can split your string in many ways. The above, on your sample string,
gives 89 characters.

Perhaps you should be more specific in what you want to do, though.


--ron

 

[Guest]

Thank you

I need to keep it in the same format as it needs to be entered into the
field with those seperators

What I am looking for I guess is to seperate out by 200 characters ensuring
that the 200th character was not going to cut a record in half

The example below has 301 characters so if I copied the the cell would lose
the extra characters

I am looking to keep them in the same format but make sure that where i
seperate is at a semi colon

 

[Guest]

i am not sure how you applied the data>text to columns>delimited>check comma
AND semicolon

 

[Guest]

hi.
on the menu bar....
data. from the drop down choose text to columns.
the the wizard comes up, click delimited on the first screen then click next.
on the next screen, in the delimiters section check semicolon and comma.
click finish.

worked for me.

Regards
FSt1

 

[Ron Rosenfeld]

I need to keep it in the same format as it needs to be entered into the
field with those seperators

What I am looking for I guess is to seperate out by 200 characters ensuring
that the 200th character was not going to cut a record in half

Here is a VBA user defined function that will parse out the segments of your
string. The arguments to the function are the string (or cell reference
containing the string); the maximum length that you want to have; and the Index
(or segment to return == e.g. 1,2,etc).

The string will not break at a separator; it also, as written, will not return
the final separator (but can if you want).

Using 89 for your sample string, the function will return the following (using
1,2,3,4 for Index):

262662,1;287740,1;266043,1;265832,1;283351,1;298864,1;292744,1;301754,1;302201,1;283346,1

296536,1;292910,1;274865,1;293748,1;77029,1;47952,1;295392,1;292327,1;273352,1;276065,1

256396,1;269669,1;269671,1;82205,1;301349,1;269668,1;260051,1;81462,1;269670,1;302623,1

281220,1;297459,1;257806,1;271241

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 it, enter a formula of the type:

=reparsestring($A$1,89,1) (returns the first max of 89 characters
=reparsestring($A$1,89,2) (returns the 2nd max of 89)

etc.

==========================================================
Option Explicit
Function reParseString(str As String, MaxLength As Long, Index) As String
Dim re As Object
Dim mc As Object
Dim sPat As String
Dim sQuant As String

sQuant = "{1," & MaxLength & "}"
sPat = "([\s\S]" & sQuant & ")[,;]|$"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat

If re.test(str) = True Then
Set mc = re.Execute(str)
reParseString = mc(Index - 1).submatches(0)
End If

End Function
======================================

--ron