Transposing inconsistent data?

[D4V1D0]

Hi All,

First post and hoping it's not too much of a basic question to start my forum career as a drain on your collective knowledge....

I've been asked to look at improving a spreadsheet/process that involves taking rows of data and manipulating it into another format.

The cells are generally not in a consistent format, but I can work with the reporting system to rectify this over time and manually correct any errors until the source data is uniform.

In short, we're asked to take a cell and convert it in the following way:

Cell:

2x apple 4x pear 1x banana

Required Result:

apple

apple

pear

pear

pear

pear

banana

I've scratched my head and haven't managed to come up with a decent solution.

I did look at "Text to columns" using the "x" as a delimiter, then removing the "x" columns, which leaves me with this:

2​

apple

4​

pear

1​

banana

From here, I'm stuck as to how I can produce the desired result. Not sure if the above actually improves anything!

Any advice would be appreciated

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	2x apple 4x pear 1x banana	apple
3		apple
4		pear
5		pear
6		pear
7		pear
8		banana
9
Data

Cell Formulas
Range	Formula
B2:B8	B2	=LET(a,TEXTSPLIT(A2,"x "," "),b,--INDEX(a,,1),TOCOL(IF(SEQUENCE(,MAX(b))<=b,INDEX(a,,2),1/0),2))
Dynamic array formulas.

 

[D4V1D0]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	2x apple 4x pear 1x banana	apple
3		apple
4		pear
5		pear
6		pear
7		pear
8		banana
9
Data

Cell Formulas
Range	Formula
B2:B8	B2	=LET(a,TEXTSPLIT(A2,"x "," "),b,--INDEX(a,,1),TOCOL(IF(SEQUENCE(,MAX(b))<=b,INDEX(a,,2),1/0),2))
Dynamic array formulas.

That is brilliant, thank you!

I'm just studying the formula to try and figure out how it works (these functions are new to me) and replicating it on my actual project. I've realised my source data will most likely be in this format:

"apple x 2 pear x 4 banana x 1"

I'm guessing this means I'll need to adjust the TEXTSPLIT part of the formula initially to recognise the fact that the x/fruit are now the other way around?

Apologies, I probably could have saved you some time by checking my facts the first time!

 

[Fluff]

Ok, how about

=LET(a,TEXTSPLIT(A2," x "," "),b,--INDEX(a,,2),TOCOL(IF(SEQUENCE(,MAX(b))<=b,INDEX(a,,1),1/0),2))

 

[D4V1D0]

That's great, I think that's cracked it for the most part, really appreciate your help on this

I think until our course data is consistent, there will be some manual workarounds needed, but this is amazing!

 

[Fluff]

You're welcome & thanks for the feedback.