Colour extract

[makinmomb]

This on a1

CL797C36
CL797C37
CL797C38
CL797C39
CL797C40
CL797C41
CL797RD36

Want this on b1 downwards

C
C
C
C
C
C
RD

[cbatrody]

Hi,

Try the following formula in B1:

=IF(LEN(A1)=9,MID(A1,6,2),MID(A1,6,1))

[makinmomb]

Worked almost on most codes except this one

CL0414C36

It pulled 4C instead of C only

[teylyn]

Yah, well, waddaya expect? How about you define the rules and the logic to be used? We can help with the formulas, but YOU need to provide the business rules.

So, for
CL797C36 you want to return 'C'

for
CL797RD36 you want to return 'RD'

At first glance it seemed to be a matter of how many characters are in the string: if the string has 9 characters, get the two characters at position 6 and 7, otherwise get the character at position 6.

Now you throw in another example that changes the goal posts.


for
CL0414C36 you want to return 'C'

So you need to re-define the goal posts.

What are the rules?

cheers, teylyn

[jason.b75]

It depends how many other variations there are, if it is always 5 characters before, 2 characters after, then this should work.

Please Login or Register  to view this content.

There are probably a few other combinations of left, right, and mid that work as well.

[samba_ravi]

deleted the post

[cbatrody]

Try the following in B1:

=IF(ISNUMBER(1*MID(A1,6,1)),MID(A1,7,1),IF(LEN(A1)=9,MID(A1,6,2),MID(A1,6,1)))

[samba_ravi]

B1=MID(SUBSTITUTE(A1,LOOKUP(9^9,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0),""),LOOKUP(1,1/MID(SUBSTITUTE(A1,LOOKUP(9^9,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0),""),ROW(INDIRECT("1:"&LEN(A1))),1)+0,ROW(INDIRECT("1:"&LEN(A1))))+1,LEN(A1))

Please Login or Register  to view this content.

Tryt this and copy towards down

[samba_ravi]

B1=MID(SUBSTITUTE(A1,LOOKUP(9^9,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0),""),LOOKUP(1,1/MID(SUBSTITUTE(A1,LOOKUP(9^9,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0),""),ROW(INDIRECT("1:"&LEN(A1))),1)+0,ROW(INDIRECT("1:"&LEN(A1))))+1,LEN(A1))

Please Login or Register  to view this content.

Tryt this and copy towards down

[:) Sixthsense :)]

Data Range

	A	B
1	CL797C36	C
2	CL797C37	C
3	CL797C38	C
4	CL797C39	C
5	CL797C40	C
6	CL797C41	C
7	CL797RD36	RD
8

Data Range

	B
1	=MID(A1,6,MAX((LEN(A1)-2)-6+1,0))

[jason.b75]

Tryt this and copy towards down

Why post the formula 4 times? The first was clearly visible.

If nothing else you get the longest formula in the thread award

[samba_ravi]

Why post the formula 4 times? The first was clearly visible.

If nothing else you get the longest formula in the thread award

While posting the formula I got some error that's why it was happen,

Please give shorter formula rather than commenting on the formula posted by me, a sweet comment will be acceptable but not criticism.
Is your formula "=RIGHT(LEFT(A1,LEN(A1)-2),LEN(A1)-7)" fulfilling the OPs requirement as post No.3 [CL0414C36 It pulled 4C instead of C only]

[teylyn]

Why don't you all wait for the OP to come back with their business logic instead of jumping the gun and wildly guessing what you think the OP may need? What's the urge?

And also, we're approaching the weekend, which means that the forum goes into glitch frenzy. Multiple repetitions of posts are the result of forum software problems, and NOT due to a member posting duplicates.

Keep it calm.

This place should be about quality, not speed.

cheers, teylyn

[teylyn]

Data Range

	A	B
1	CL797C36	C
2	CL797C37	C
3	CL797C38	C
4	CL797C39	C
5	CL797C40	C
6	CL797C41	C
7	CL797RD36	RD
8

Data Range

	B
1	=MID(A1,6,MAX((LEN(A1)-2)-6+1,0))

Well, how about post #3 then?

[:) Sixthsense :)]

Well, how about post #3 then?

I used 3 functions whereas in Post #3 it was 4 lol

[makinmomb]

The only one that worked was this one by CBTRODY

Please Login or Register  to view this content.

[makinmomb]

It pulled C on CL0414C36 while the other formula by others pulled 4C
It takes cares of the length of description regardless

[makinmomb]

Ouch but it pulls T instead of TN on this

KD646TN2

[makinmomb]

Here is the file attached in case to see character variations

[jason.b75]

Please give shorter formula rather than commenting on the formula posted by me, a sweet comment will be acceptable but not criticism.
Is your formula "=RIGHT(LEFT(A1,LEN(A1)-2),LEN(A1)-7)" fulfilling the OPs requirement as post No.3 [CL0414C36 It pulled 4C instead of C only]

I missed that example in post #3. I will give criticism and accept it where given to me, I know I make mistakes, and I like to know when I do.

Giving a sweet comment to a bad answer is like praising a pet that has just left a smelly package in your brand new carpet

The OP has accepted a solution, which, in my opinion, doesn't need improving so no point in trying to find and posting another.

[samba_ravi]

B1

Please Login or Register  to view this content.

[makinmomb]

wow NFSALES , this is it

[makinmomb]

It even took care of this

CM122DBR41 DBR

[teylyn]

makinmomb,

you threw a question at us and several folks attempted to answer it.

There were a few questions for clarification. You seemed to ignore these.

Your issue is not straightforward. You need to answer our questions so we can address your issue.

Please review the posts above and address the questions posted by people who are trying to help you.

[jason.b75]

There were a few questions for clarification. You seemed to ignore these.

Your issue is not straightforward. You need to answer our questions so we can address your issue.

It was straightforward enough,
4 part string consisting of text & number & text & number, with no delimiters to make use of.
Requirement to return the second text portion, i.e. the third part of the string.

You were the only person that asked more questions, everyone else figured it out easily enough, we were all just guilty of posting answers without testing them properly.

I may have harshly criticised nflsales earlier attempt (whilst failing to see the flaws in my own offering), but their latest was accepted by the OP as returning all of the results as expected.

[teylyn]

Jason, I'm trying to encourage the OP to clearly state the requirements, instead of letting us guess what the result should be. Yes, I did figure it out, too. I just wanted the OP to confirm the assumptions we all made. I've been in the business long enough to know that sometimes a half dozen examples does NOT cover all the possibilities of the real world.

Not trying to be difficult, but wanting as much info as possible to avoid wasting time on wild goose chases.

[jason.b75]

Just found this in another thread

One such rule might be return the characters before the first digit

Please Login or Register  to view this content.

I've only just upgraded from excel 2007 so not had much chance to figure out the aggregate function, but the one above got me thinking that it could be applied here.

Bearing in mind this is my first attempt at an aggregate derived formula, there is probably room for improvement, but it does return all of the correct results, and I don't think any of the functions are volatile.

Please Login or Register  to view this content.

It is (slightly) shorter than nflsales formula, and does appear to be slightly more efficient, but I haven't done performance testing, only compared results.

edit:-

One subsequent observation, the formula will not recognise lower case characters, a failsafe here, if needed would be to change the 2nd and 6th instances of A1 to UPPER(A1).

The formula can be shortened quite a bit if the string will always have 2 letters before the first number.

Please Login or Register  to view this content.

Note that this short version doesn't include the lower case character failsafe mentioned above, although it could still be applied.