# Finding Next Letter..

#### Ste_Moore01

##### Active Member
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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)

Possibly:

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

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

Best regards

Richard

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

In my data though it is set out like..
Book2
ABCD
110L 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
ABCDEFGH
110L X 3M = 30MFTS4W165.630M3165.693.50%10.76
Sheet1

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

i.e.
Code:
``````Sub Macro1()
a = Split([a1])
b = 1 + UBound(a)
Range([a3], Cells(3, b)) = a
End Sub``````

It does nothing.

Any body any suggestions?

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

a = Split([a1])

to

a = Split([a1], " ")

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

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.

Replies
2
Views
164
Replies
3
Views
107
Replies
5
Views
1K
Replies
2
Views
197
Replies
1
Views
289

1,206,971
Messages
6,075,922
Members
446,170
Latest member
zzzz02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back