How to remove extra data from a cell

[alexduy]

Is there a formula to remove anything that follow the second space in a cell. For example, I have the following in cell A1, "Alex Hernandez Job#134 Department 124 Space 100". The formula will return just "Alex Hernandez". I know I can do text by column and delete the extra words, but some names only follow by Job#134 and some names follow by many more information. Thanks in advance.

[XOR LX]

Hi,

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),2*LEN(A1)))

Regards

[alexduy]

Thanks! Can you help me incorporate that function into this function that I wrote? =PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F3, ",", " "), "-"," "), "H&W", "")))

[XOR LX]

Not sure. Where should it go?

Even better, why don't you explain what you're trying to do and give an example or two? You never know, there might be a simpler solution!

Regards

[alexduy]

In cell A1, I have the following "H&W-MOLINA, TIMOTHY Job#103"
I want to remove everything and return "Molina,Timothy" . Basically, the function I wrote was to remove all "commas" and "H&W", "-", etc and return "Molina Timothy Job#103". I want to write an additional function that would remove anything that is follow after Timothy. Thanks!

[XOR LX]

But I take it this isn't a one-off exercise, to be performed just on that one string?

If not, and so you need a solution which can work in multiple cases, what general statements can you make about where the extraction should take place?

For example, is every case of the following precise format:

H&W[a single hypen][some surname][a single comma][a single space][some first name][a single space][some random irrelevant text]

and you want to return:

[some surname][a single comma][a single space][some first name]?

Regards

[sktneer]

For one single string example which you showed, the following formula will give you the desired output.
Assuming your string is in F3, then

Please Login or Register  to view this content.