Finding Next Letter..

[Ste_Moore01]

Hi,

I have some text strings which begin with many spaces (" ") and I'm trying to write a formula that ignores these spaces and tells me the next word.

Is this possible without resorting to VBA?

If it isn't can anyone help me with the coding of the VBA?

Thanks guys!

 

[steallan]

not to sure if this is the type of thing you want but it might help.

If your not bothered about the spaces you can use a function in ASAP Utilities Excel Add-on to delete all the leading spaces, leaving you with just the words, and then you dont have to use VBA

If this is no good get ASAP utilites anyway it is amazing. Just search for it in google (it's free)

 

[Richard Schollar]

Possibly:

=LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)

Assuming your text string is in A1 and there are spaces between words.

Best regards

Richard

 

[Ste_Moore01]

That works if there is only 1 word in the cell.

In my data though it is set out like..

Book2
	A	B	C	D
1	10L X 3M = 30M FTS4W 165.60 30M 3 165.60 93.50 % 10.76
Sheet1

and I'd like to set it up like

Book2
	A	B	C	D	E	F	G	H
1	10L X 3M = 30M	FTS4W	165.6	30M	3	165.6	93.50%	10.76
Sheet1

The only problem is, if I used a VBA split code, like this one to

i.e.

Sub Macro1()
    a = Split([a1])
    b = 1 + UBound(a)
    Range([a3], Cells(3, b)) = a
End Sub

It does nothing.

Any body any suggestions?

 

[Richard Schollar]

Can you use Text-To-Columns with space delimiters?

 

[Ronald Moore]

In your code, change

a = Split([a1])

to

a = Split([a1], " ")

 

[Ste_Moore01]

No it would have to be 2 or more spaces rather than just 1 as "10L X 3M = 30M" has spaces.

 

[Ste_Moore01]

I think I may be able to get this working...

Trying to use the find function to find a space it would be
=FIND(" ",A1,1)

If you wanted to find anything but a space what would you use instead of the " "?

This would help me to get it working.