String Manipulation - Split into multiple variables

[Dulanic]

Hello,

I have a string that is pulled in from a 3rd part app. It ends up looking like this:

1234-56JUL10 2345678JUL10 3456789JUL10 876543JUL10 987654JUL10

It is all one string.... I need this to be able to have a variable that can be updated to the position in the string. So for example I need the 3rd portion is there any way to specifically pull the 3rd section of the string?

To explain a little, each of these are basically "claims" and I need to be able to check them one at a time which I would use a counter variable like d = d + 1. I know I could pull this value into a cell, use text to columns and then just use Cell to keep moving over 1 column until I found what I needed, but is there a way in VBA without pasting the values into a cell and using text to columns?

Edit: See end of thread for more info. I can't easily use a function.

[stanleydgromjr]

Dulanic,

Detach/open workbook SplitData - Dulanic - EF744090 - SDG12.xlsm and run macro SplitData.

You only gave one example of the string.

I assume that all your data in column A, except for the beginning and ending spaces, contains 24 space characters between the strings you need.

[Leith Ross]

Hello Dulanic,

This macro is a User Defined Function that will allow a variable number of spaces before and after the claim numbers. Copy this code into a standard VBA Module in your workbook.

Please Login or Register  to view this content.

Macro Example

Please Login or Register  to view this content.

[stanleydgromjr]

Leith Ross,

Very nicely done.

I just received:
The book 'Sams Teach Yourself Regular Expressions in 10 Minutes' by Ben Forta

Can you suggest any other books on the subject?

[Leith Ross]

Hello Stanley,

Sorry, I haven't got any books on Regular Expressions, nor have I read through any. I have learned it through using it. Most of the information I have seen has been for Java, or PHP. There seems to be little specifically for VBScript RegExp. I will have to check out the Sams publication. Thanks for information.

[stanleydgromjr]

Leith Ross,

You are very welcome.

How would I, you, change your pattern, to work with a long string that does not end in a space or spaces?

Please Login or Register  to view this content.

[Leith Ross]

Hello Stan,

I am not sure what would need to be changed. The pattern operator \s+ will look for one or more spaces. The operator (.*) will hold the remainder of the string. You could change it to \s* which would look for zero or more spaces. Did you want to test for something else?

[stanleydgromjr]

Leith Ross,

If I use your function with Dulanic's original string (without the " marks):

" 8927-27JUL10 4601107JUL10 1783421JUL10 3511826JUL10 9886-09JUL10"

And use the formula:
=GetClaim(A1, 5)

The result is blank.


If I add a trailing space to the Dulanic's original string (without the " marks):
" 8927-27JUL10 4601107JUL10 1783421JUL10 3511826JUL10 9886-09JUL10 "

And use the formula:
=GetClaim(A1, 5)

The result is:
9886-09JUL10

[Dulanic]

Hello,

Thank you all for the code, I appreciate it. Is it possible to do this without posting the data to the workbook? These "claims" do not need to be seen by the user as it is going to be part of a larger macro. This macro interfaces with another app and checks each claim to see if it matches the rest of the data. This string is never shown to the user nor is it placed into the excel workbook, it is purely stored as a variable. I guess I could have the macro put the claims on a worksheet, but then I think it may just slow down the macro, this macro already takes about an hour to run since it interfaces with another program. Hope that makes sense?

Here is a very dumbed down basic example of what I am doing:

Please Login or Register  to view this content.

I also see what Stan was saying. My string can be variable, it may or may not end in spaces. It seems if I use ...

Please Login or Register  to view this content.

This seems to always work

[Marcol]

Another UDF might be

Please Login or Register  to view this content.

Or called in VBa

Please Login or Register  to view this content.

will return the first value

Please Login or Register  to view this content.

Returns the second

Please Login or Register  to view this content.

Uses "," as the separator instead of the default " " (Space).

Hope this helps

[Dulanic]

I think that might do it. Just use a variable so I can do Your UDF doesn't work for me, however, I think I can twist Leith Ross's into doing what I need just by setting it like so:

Please Login or Register  to view this content.

to roll through each of them until the result is what I need or I hit a certain number.

I appreciate all the help and will mark solved once I get a chance to test and implement.

[Dulanic]

Not quite there, I am getting closer This below code using Leith's code. However, it stops 1 claim prior to the last claim. I tried

Please Login or Register  to view this content.

This throws an error. I was hoping to add a leading and trailing space to force it to work. Using just adding the trailing space didnt seem to change the results.

Using =GetClaim($A$1 & " ",1) just throws a #VALUE result.

The preferable way for this to work for me would be:

Please Login or Register  to view this content.

I can do this to force it, but then I just keep adding spaces each time it's run.

Please Login or Register  to view this content.

[Marcol]

It seems to work for me.

See attached workbook

[Dulanic]

Odd don't know why it wasnt working for me. OK so leading that into my other question, is it possible to fit this into VBA only with no reference to a cell as I indicated above?

[Marcol]

I'm not sure what you are trying to do

However try this modification to the UDF and apply it in VBa as explained in post #11

Please Login or Register  to view this content.

[Dulanic]

Sorry if I did not explain well enough. My goal is to avoid using the worksheet itself to do this, the string is originally pulled in to a variable via code to grab it from the 3rd party app. I want to try and avoid actually putting the value on the worksheet because noone needs to see it until it finds a match, does this make sense? Basically is there a way to make it allow the 1st variable for GetClaimFrom allow a variable input instead of a range input?

Please Login or Register  to view this content.

[Dulanic]

Got it, sorry for all the extra work and me not explaining it that well. This was all I needed, i just haven't used Split all that much and didn't realize all I needed to do was provide the delimiter and the position after. I do have to admit the (v) for the position afterword kind of throws me off and not sure how that works out. I mean I understand what it did, just the layout of it doesnt make sense to me.

Please Login or Register  to view this content.

[snb]

Use the builtin facilities of vba

If you split a string into an array you can filter it and check the filtering result. No need for a Do.. or For.. loop.

Please Login or Register  to view this content.

[Marcol]

Try this in a blank sheet

Please Login or Register  to view this content.

This, based on your sample string, will split your string then divide again into Code and date (as a string).

WorksheetFunction.Trim() is used rather than just Trim() because it will remove all extra spaces, not just the leading and trailing spaces.

Hope this helps

[Leith Ross]

Hello Dulanic,

Here is the corrected version of my macro. The fix was to add a space to the end of the Claims and change Claims from a Range to a String.

Please Login or Register  to view this content.

[snb]

if string c01 contains all claims as 1 string you can get the third claim using:

Please Login or Register  to view this content.

[Dulanic]

OK so I am reopening this thread as I am finding what I was doing isn't working.... I was using this:

Please Login or Register  to view this content.

v is a variable interger. The problem comes in if it is not able to find a match it tried to keep going to let's say it does v as 6. This results in an error since 6 is not a valid option since it only split 5 claims. Is there a way to prevent this error?

I guess what I am wants to find out is can I get a count for "Claim" since it breaks down into multiple variants and I want to know how many? Like there is Claim(0) through Claim(5) so I want to know the highest variant is 5 so I can throw a catch to stop at 5. Sometimes 5 mights be 4 or 7....

Thanks to those for functions, but functions arent going to really do what I need since I am trying to get variables purely in VBA and not involve a worksheet.

[shg]

What would you want to return if it were beyond the last value -- a null string?

Please Login or Register  to view this content.

[Dulanic]

What would you want to return if it were beyond the last value -- a null string?

Please Login or Register  to view this content.

Looks to be working because then I could toss in....

Please Login or Register  to view this content.

[snb]

I answered your question before posted it:

Please Login or Register  to view this content.

[Dulanic]

I answered your question before posted it:

Please Login or Register  to view this content.

I appreciate the help snb. While I saw your solution, it did not fit into what I needed. Sorry if I didn't explain it well. While I knew the (1) etc... could be variable to do what I needed, I had no idea what the last claim technically was. So what I needed I had to have it continue until it hit no claim and then do something else. Since the amount of claims were variable split would throw an error if I went all the way split with (6) but there are only 5 claims.

I really do appreciate your help however and you have been helpful to me in the past also.

[snb]

Please Login or Register  to view this content.

This means: it returns the last item form string c01, that has been split by a space. This is a variable way of returning values and it can't return an error.

e.g
c01 is string "aa bb gg tt uu"
last_item will return 'uu', using

Please Login or Register  to view this content.