Left, Mid & Right with variable lenght

[GLOBETROTTER]

Hi Guys, been following the forum for ages and my time has finally come. I'm banging my head over the following table.

C	D	E	F
143CTN2120 <tbody> </tbody>	143	CTN	2120
3M47516 <tbody> </tbody>
180SP129611 <tbody> </tbody>
2SP158618 <tbody> </tbody>

<tbody>
</tbody>

Column C contains hundreds of the values as per the above example. I have to separate each value (C) as per the example in C1. Unfortunately all are coming in different length which render Text to Value and simple Left/Right useless. Can anyone help with formula to split the values in C even if I have to use individual formulas for D, E and F. Cheers

 

[skorpionkz]

but still why the formula doesn't work if there is more than 4 digits at the end of the text?

Excel 2010
	C	D	E	F
3	35JUN12	35	JUN	12
4	2RP6145	2	RP	6145
5	3SP152748	3SP15		2748
Sheet3

Cell Formulas
Range	Formula
D3	=SUBSTITUTE(C3,E3&F3,"")
E3	=RIGHT(SUBSTITUTE(C3,F3,""),MATCH(TRUE,ISNUMBER(--LEFT(RIGHT(SUBSTITUTE(C3,F3,""),{1,2,3,4,5,6,7,8,9,10,11,12}),1)),0)-1)
F3	=-LOOKUP(1,-RIGHT(SUBSTITUTE(C3,{"E","U","A","O"},"X"),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))

 

[XOR LX]

You can't array-substitute like that!!

That's what makes the idea even worse: you would have to have a separate SUBSTITUTE formula for each, i.e.:

...RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"E","X"),"U","X"),"O","X",...

etc.

Regards

 

[BJungheim]

Edit: XOR LX already explained the issue.

 

[XOR LX]

Because when you introduce an array into the second argument of Substitute it returns an array which causes the Right function to only work for as many characters as there were elements returned by Substitute (if that makes sense). You either have to keep the second argument of Substitute to a single entry or expand it to match the Right function's argument, i.e.:

=-LOOKUP(1,-RIGHT(SUBSTITUTE(C5,{"E","U","A","O","X","X","X","X","X","X","X","X","X","X","X","X","X","X","X","X"},"X"),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))

Actually, if we transpose the first array, we can have:

=-LOOKUP(1,-RIGHT(SUBSTITUTE(C3,{"E";"U";"A";"O"},"X"),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))

On a string in C3 of e.g. 3SJUN15, the SUBSTITUTE part will then yield an array consisting of four elements:

{"3SJUN15";"3SJXN15";"3SJUN15";"3SJUN15"}

However, none of these has all the desired substitutions, so we are left with the same issue when we pass it to the LOOKUP.

Regards

 

[BJungheim]

Actually, if we transpose the first array, we can have:

=-LOOKUP(1,-RIGHT(SUBSTITUTE(C3,{"E";"U";"A";"O"},"X"),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))

On a string in C3 of e.g. 3SJUN15, the SUBSTITUTE part will then yield an array consisting of four elements:

{"3SJUN15";"3SJXN15";"3SJUN15";"3SJUN15"}

However, none of these has all the desired substitutions, so we are left with the same issue when we pass it to the LOOKUP.

Regards

I realized that after I posted, which is why I edited my post. Oops!

 

[XOR LX]

Edit: XOR LX already explained the issue.

Actually I thought that was a very interesting and informative post: you should leave it in!

Regards

 

[skorpionkz]

Ok I see.

Basically it will not substitute all of them at once, but will create an array with all variations ob substitute.

 

[XOR LX]

Ok I see.

Basically it will not substitute all of them at once, but will create an array with all variations ob substitute.

Indeed. But it really is time Microsoft made such a version of SUBSTITUTE. And CONCATENATE while they're at it.

Regards

 

[XOR LX]

Hmmm. Against my better judgement, I may have found a way to "save" this approach:

=-LOOKUP(1,-(" "&RIGHT(SUBSTITUTE(C3,"E","X"),{1,2,3,4,5,6,7,8,9,10,11,12})))

in which I think the space will now force Excel to error on dates (e.g. JUN13) within the string. Still need to account for scientific, though (hence the substitution), and I'm still not convinced...

Regards

 

[Rick Rothstein]

Hmmm. Against my better judgement, I may have found a way to "save" this approach:

=-LOOKUP(1,-(" "&RIGHT(SUBSTITUTE(C3,"E","X"),{1,2,3,4,5,6,7,8,9,10,11,12})))

in which I think the space will now force Excel to error on dates (e.g. JUN13) within the string. Still need to account for scientific, though (hence the substitution), and I'm still not convinced...

It fails on something like this ABC2% assuming 0.02 is not the required return value. It also fails on something like this ABC(2%) assuming -0.02 is not the required return value.