Another String Manipulation

[week]

My String looks like this:

"Pepsi ILMutual 2003/07/01249"

I will need to extract Pepsi, IL, Mutual, 2003/07/01249 separately. There are more than one blank spaces where it's blank.

I know you guys will come up with something in no time...thanks for the help!

 

[Rob892]

I recently had to deal with a similiar situation. I used the split function to split the string I.E.
Dim words() As String
words() = Split(strInput, Chr(32))
This will assign elements of strInput to the words array, the elements of the array will be split by the space character(Chr(32). A little more processing, and you should be able to extract the values you need from the string. I hope this helps you out.
Rob

 

[YoshiCD]

week,

It would help if you came up with at least 2 strings so we could know if the placings of the things you want to extract are standard or not.

First, for instance, lets say the the part that holds 'PEPSI' (i.e. the first part up until 'IL') can hold up to 9 characters. Then
[tt]
TRIM(MID$ ("Pepsi ILMutual 2003/07/01249",1 ,9))
[/tt]
(Taking 9 consecutive characters starting from the First and removing any redundant spaces)
-OR-
[tt]
TRIM(LEFT$ ("Pepsi ILMutual 2003/07/01249", 9))
[/tt]
(Taking the 9 Leftmost Characters and removing any redundant spaces)

Both will work fine, providing 9 is the correct amount.

Now, staying on the basis that is 9 character spaces at the start (including your PEPSI) then the Characters 'IL' Will be character numbers 10 and 11, so...

[tt]
TRIM(MID$ ("Pepsi ILMutual 2003/07/01249", 10, 2))
[/tt]
(Taking 2 Consecutive Characters starting at the 10th and removing any redundant spaces)
is what you need.

Next, assuming the space containing 'Mutual' can hold 12 Characters and, of course, that previous assumptions are correct, then to return 'Mutual' this you need...

[tt]
TRIM(MID$ ("Pepsi ILMutual 2003/07/01249", 12, 12))
[/tt]
(Taking 12 Consecutive Characters starting at the 12th and removing any redundant spaces)

Lastly, If the amount of Numbers at the end are standard then the following functions

[tt]
TRIM(RIGHT ("Pepsi ILMutual 2003/07/01249", 14))
[/tt]
(Taking the 14 rightmost characters and removing any redundant spaces)
-OR-
[tt]
TRIM(MID$ ("Pepsi ILMutual 2003/07/01249", 24, 14))
[/tt]
(Taking 14 Consecutive Characters starting at the 24th and removing any redundant spaces)

will both return [tt] "2003/07/01249" [/tt]

A Final Note, when using String Manipulation Functions, it's always good practice to store your string in a variable first, for example,

[tt]
DIM strMyString as String

strMyString = "Pepsi ILMutual 2003/07/01249"
[/tt]

Then use this variable in the functions, for example...

[tt]
TRIM(MID$ (strMyString, 24, 14))
[/tt]

Hope this helps, if you need any more help email me.

===========QUOTE=============
"Pepsi ILMutual 2003/07/01249"

I will need to extract Pepsi, IL, Mutual, 2003/07/01249 separately. There are more than one blank spaces where it's blank
=========END QUOTE===========


¥oshi

-------------------------
"There is No Spoon.."
-------------------------

 

[Hypetia]

>There are more than one blank spaces where it's blank.
___

Dim S As String, S2 As String, V As Variant, N As Integer
S = "Pepsi ILMutual 2003/07/01249"
Do
S2 = S
S = Replace$(S2, " ", " &quot
Loop Until S2 = S
V = Split(S)
For N = 0 To UBound(V)
Debug.Print N, V(N)
Next

 

[week]

Thanks so much for your inputs.