Excel formulae to extract text string from a cell

[simongilder]

Hi,

I am having problems with an Excel formula, I was wondering if anyone can help?
In column A, I have cells with a mix of numbers and text. The string is built according to:

Company Name + B/S+ Number + Customer Name + Reference

Rus-Hydro B 120 Smith 123456789


All elements can vary in length. I want to extract the Customer Name(eg Smith). I considered the Find and Mid function combination, but because everything varies, I can't get it to work.

Can anybody help?

Many thanks

Simon

 

[XOR LX]

Sorry to be overly-rigorous and to spoil this (obviously quite fun!) formula-party, but I can't help noticing that, 20 (!) posts in, not only have a mere 2 (!) examples of strings been provided against which to test potential solutions, but also no-one seems to have taken the time out to ask what the actual logic is which defines where the extraction should take place in all cases.

For example, based on those two examples given, I would be first tempted to hypothesize:

"The desired extraction is always to be found between the only two numbers in the string."

and to ask the OP to clarify if that hypothesis was correct, and, if not, to then ask if such a generalized statement could be made. Only then would I attempt to formulate a solution.

Like I said, sorry to spoil the party, but I get the feeling that a lot of that (obviously time-consuming and certainly impressive) formula-work could have been better served if a few simple questions had first been asked...

Regards

 

[Rick Rothstein]

Sorry to be overly-rigorous and to spoil this (obviously quite fun!) formula-party, but I can't help noticing that, 20 (!) posts in, not only have a mere 2 (!) examples of strings been provided against which to test potential solutions, but also no-one seems to have taken the time out to ask what the actual logic is which defines where the extraction should take place in all cases.

That is what I was driving at in Message #18, but the OP hasn't responded to it yet.

 

[XOR LX]

That is what I was driving at in Message #18, but the OP hasn't responded to it yet.

Yes. In this type of question one of two things is necessary:

1) A sufficient number of examples such that comprehensively cover all variations in string type to be considered and which together demonstrate clearly the logic defining where the extraction is to be made.

2) A well-defined, rigorous and logical statement which defines where the extraction is to be made for all cases.

Ideally both. In practice neither.

Regards

 

[NiceCyte]

Yes. In this type of question one of two things is necessary:

1) A sufficient number of examples such that comprehensively cover all variations in string type to be considered and which together demonstrate clearly the logic defining where the extraction is to be made.

2) A well-defined, rigorous and logical statement which defines where the extraction is to be made for all cases.

Ideally both. In practice neither.

Regards

I don't think 1) is realistic, since the original post said that all fields can vary in length and, also, since a company name by itself can have a numerous amount of variations with spaces, odd punctuation, etc.

I think 2) is possible based on the ways that Customer Name can be expressed. It's unclear as to whether it will always be a single name, or if it can be multiple names like John Booth, or John Wilkes Booth.

Somehow, the purpose of the exercise got away from how to extract the Customer Name and evolved into ways to extract Company Name. My previously stated method was under the assumptions that a) the Customer Name was embedded between the last and second to last space, and b) the Customer Name had no spaces.

Even that, in my opinion, has too many assumptions without knowing where the data is coming from.

So I think the real solution here... is to tell programmers to not give us space delimited data! Pipes, man! Pipes!

 

[hippiehacker]

it would be nice to see some more examples before you can go further in discussion otherwise we are just guessing how the data looks like

 

[simongilder]

Hi everybody,

first, thanks so much for all your contributions and time spent on this problem. I shall endeavour to get more information on the task logic, with examples, to clarify the task at hand, in the next 24 hours. For my part, I can say that this is very instructive and has in any case allowed me to overcome my practical problem that I was facing. But it would great to "nail it", if at all possible.

Thanks and regards,

Simon