Formula to breakout text from a cell, multiple times.

[knoll126]

Hi,

I am trying to think of a formula to enable the data in column A to look like the data in columns B through I. My goal is to get whole numbers that are 3 digits long. The three digits that need to be populated are after the dashes for the positions. The positions would dictate what column it goes into. So for cell A2, it falls into cell C1, because of the "1B" in front of the dash. "101" is populated because it has a leading "1" after the dash and the "01" is populated because it is a singular 1 after the "e".

It is messy data and there are many complications on breaking this out and I don't know where to start.

The first issue I am seeing is in the second row we can see multiple entries. The most complicated being "rf/lf-5(+2)e12". These results are the same for each position and the data I am provided list them in the same "chunk" of data in the cell. I would hope this would go into the two different columns, G and I. We also ignore anything that is a parenthesis and only care about the leading number after the dash and the following 2 numbers after the "e".

The 2nd complicated piece is the example on row 4. RF and CF data set in the cell only has 1 leading digit and that is because the "e" number will be the same as the first LF number. Again I would like these to be broken out into the respective columns if possible.

The last set of complicated numbers is the CA position. Like the RF/LF/CF prefix this will come with data that is in a parenthesis. This number needs to be ignored and only have the leading number and the number directly after the "e" be put into cell B13.

To clarify a bit, I am trying to reproduce columns B through I. The original data I get is only column A. I would like to be able to apply a formula to get the results in column B through I.

Any help is appreciated.

Strato Error Rating Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Error Rating	C	1B	2B	3B	SS	LF	CF	RF
2	1b-1e1		101
3	1b-4e12, 3b-4e16, rf/lf-5(+2)e12		412		416		512		512
4	lf-2(0)e3, rf-3, cf-4, 2b-4e24			424			203	403	303
5	2b-3e11			311
6	1b-4e7		407
7	3b-3e14, 1b-4e30		430		314
8	cf-2(-1)e2						202	202	202
9	lf-5(-3)e6						506		506
10	ss-2e12					212
11	of-4(-1)e9						409	409	409
12	1b-4e5, rf/lf-3(+1)e22		405				322		322
13	CA-2(0)e4t10p0, 1b-4e25	204	425
Sheet1

Also asked here Formula to breakout text from a cell, multiple times.

 

[fxjunction]

This looks like some kind of Baseball error notation right? I know you said you wanted the information on the right to look like the left, but some of your statements made it sound like you want the information on the left broken out onto the right. If you are trying to condense the information from the right to the left you could use the =concatenate formula and have it expressed in one cell easily enough. It would be harder to figure out when to skip blanks then to get the information all in the left column. If you are having trouble with the cell formatting wanting to convert your strings over you can put a ' before the number and it will be considered a string, and it should prevent zeros from going away without having to fiddle with the cell formatting.

 

[knoll126]

This looks like some kind of Baseball error notation right? I know you said you wanted the information on the right to look like the left, but some of your statements made it sound like you want the information on the left broken out onto the right. If you are trying to condense the information from the right to the left you could use the =concatenate formula and have it expressed in one cell easily enough. It would be harder to figure out when to skip blanks then to get the information all in the left column. If you are having trouble with the cell formatting wanting to convert your strings over you can put a ' before the number and it will be considered a string, and it should prevent zeros from going away without having to fiddle with the cell formatting.

Yes it is for baseball!

To clarify a bit, I am trying to reproduce columns B through I. The original data I get is only column A. I would like to be able to apply a formula to get the results in column B through I.

 

[knoll126]

This was solved with help from a different forum.

Formula to breakout text from a cell, multiple times.

 

[fxjunction]

I found a combination of formulas that works for some of your criteria, but has a hard time with some of the formatting but maybe it helps a little. Using the example table you gave earlier I put the following formula in cell C2, so 1B first error. Each column looks for the two letter position in the heading and then pulls a string of characters starting after the dash up to the next comma. I know this doesn't fully answer your problem, but it might be a start if you hadn't made it this far.

=IFERROR(MID($A2,FIND("1b",$A2)+3,FIND(",",$A2,FIND("1b",$A2)+3)-FIND("1b",$A2)-3),"")

Error Rating	C	1B	2B	3B	SS	LF	CF	RF
1b-1e1,		1e1
1b-4e12, 3b-4e16, rf/lf-5(+2)e12,		4e12		4e16		5(+2)e12		lf-5(+2)e12
lf-2(0)e3, rf-3, cf-4, 2b-4e24,			4e24			2(0)e3	4	3
2b-3e11,			3e11
1b-4e7,		4e7
3b-3e14, 1b-4e30,		4e30		3e14
cf-2(-1)e2,							2(-1)e2
lf-5(-3)e6,						5(-3)e6
ss-2e12,					2e12
of-4(-1)e9,
1b-4e5, rf/lf-3(+1)e22,		4e5				3(+1)e22		lf-3(+1)e22
ca-2(0)e4t10p0, 1b-4e25,	2(0)e4t10p0	4e25