Hello,
I was wondering how I could write a formula that could pull words from a
sentence in a single excel cell and put them in another cell.
For example, if the following is in a single cell:
Webb, John runs 8 yards
How can I pull "Webb" or "run" or "8" out of the
statement to place in it's own cell?
Thank you in advance!
First you need to define or requirements more precisely.
By "pulling Webb" do you mean the "first word" in the string?
do you mena "all the characters before the first comma?
Same with your other "words", as well as your special case of not pulling a full word (you want to return "run" and drop the terminal "s" in your example; reliably detecting if a word which ends with "s" is plural or not would require an extensive database).
How are you defining "run"? Is it the third word? is it the second word after the comma? Is it the first word before the number?
Here are some examples
To return the first substring up to a comma:
=LEFT(A1, FIND(",",A1)-1)
User Defined Functions (UDF) can be used to simplify complicated criteria.
For example, with this simple UDF:
======================
Option Explicit
Function ReExtract(s As String, p As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.ignorecase = True
re.Pattern = p
ReExtract = "Pattern does not match"
If re.test(s) = True Then
Set mc = re.Execute(s)
ReExtract = mc(0)
End If
End Function
========================
you can devise patterns to match various criteria:
First substring ending with a comma:
=ReExtract(A1,"^.*(?=,)")
First word before the first digit in the string:
=ReExtract(A1,"\w+(?=\s\d)")
First word before the first digit but omit any terminal "s":
=ReExtract(A1,"\w+[a-rt-z](?=s?\s+\d)")
First digit string:
=ReExtract(A1,"\d+")
(to return the first number, where there may be decimal places, require a more complex pattern.
And so forth.
