MID Function w/ Variable Field Length and Spaces

[crew696]

Good Day,

I am trying to break the data in column "A" into four different components as shown in columns B,C,D. and E.

1) I can extract the data for column "B" by using the formula: =LEFT(A2,FIND(" ",A2)-1)

After that, I am pretty much stuck because the text varies in length between the blank spaces? Any suggestions?

Thanks

[DonkeyOte]

Perhaps:

Please Login or Register  to view this content.

if content is always numeric and you want to return numbers coerce accordingly - ie add +0 to the above
(for numbers - where coercing and where no. items may vary per string you can wrap the above+0 in an IFERROR to return a Null where no number exists)

[crew696]

Thanks for the quick response. The solution does indeed work for extracting the information for Column "B", but how would I go about extracting the values for Columns C, D, and E? And sometimes the data can be alphanumeric. Thanks.

[DonkeyOte]

As inferred by "apply to results matrix" the formula dragged across will return each term in succession until such time as no more terms may be found.

[crew696]

Brilliant! Thank you so much.

[MarvinP]

Hi crew696
Here is another formula that DO taught me a few months ago.

Please Login or Register  to view this content.

Put the above formula in B2 and pull formula to the right and then down to get you answers.
Find the attached..

[DonkeyOte]

Yes, I forgot to add the TRIM to my prior suggestion - now edited - thanks
I had originally coerced to number then in hindsight removed the coercion and forgot to add back the TRIM

@MarvinP, IMO better to use COLUMNS construct than COLUMN()+adjustment ... should you insert a column between say A & B the latter approach will cease to function correctly.

edit:
Further to above point... COLUMNS returns a scalar 1 whereas COLUMN actually returns an array {1}
In this instance this won't affect result but given we want a scalar here it makes sense (IMO) to use the appropriate function.

[MarvinP]

After some testing (my signature line) it looks like inserting Column A or B is the only time a problem occures.

I accept you opinion as valid. BUT - it was hard enough to come up with the "blow up the spaces" formula on my own. I couldn't find the first time you showed this to me. It was like magic. It took me about 5 tries at the formula to understand. I really think the first time you showed this (trick?) to me the formula was shorter.

Thanks for being my/our teacher.