Need help stripping different length texts into two parts

[zrall]

As an example, I have the following sets of accounts. Under Account is what I have, and Number and Type are what I need returned. As a disclaimer, none of these are real accounts, but the lengths are all representative.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Account
[/TD]
[TD]Number
[/TD]
[TD]Type
[/TD]
[/TR]
[TR]
[TD]*ZHR|U1234567
[/TD]
[TD]U1234567
[/TD]
[TD]*ZHR
[/TD]
[/TR]
[TR]
[TD]STID|U1234567 1
[/TD]
[TD]U1234567
[/TD]
[TD]STID
[/TD]
[/TR]
[TR]
[TD]IR|Q45678989
[/TD]
[TD]Q4567898
[/TD]
[TD]IR
[/TD]
[/TR]
[TR]
[TD]DQW|ITERW|Q4765567 1
[/TD]
[TD]Q4765567
[/TD]
[TD]DQW|ITERW
[/TD]
[/TR]
[TR]
[TD]CVR|L1425690
[/TD]
[TD]L1425690
[/TD]
[TD]CVR
[/TD]
[/TR]
</TBODY>[/TABLE]

All of the Number columns need to be the first letter followed by 7 digits, although some of the Accounts have 8-9 characters after the letter. The Type column needs to strip off the final "|" and anything following that (again tricky because of differing character lengths). If it matters, there are a total of 48 different Type categoris and 4 different possible numbers before the 7 characters in the Number categories. Also, the number combinations are seemingly infinite.

I'm pretty certain this requires VBA to perform. I've been messing around with it all day but haven't been able to get anywhere. I would greatly appreciate some help with this.

Thanks.

 

[Rick Rothstein]

Put these formulas where indicated and then copy them down...

B2: =TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ")-1),"|",REPT(" ",99)),99))

C2: =LEFT(A2,FIND(B2,A2)-2)

 

[jai9]

Hi Rick,

Can you please explain this part "|",REPT(" ",99)),99) in the formula?

I have seen this being used many times in text extraction formulas. But cant figure out exact thing it does.

Thanks in advance!!

Jai

 

[zrall]

Put these formulas where indicated and then copy them down...

B2: =TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ")-1),"|",REPT(" ",99)),99))

C2: =LEFT(A2,FIND(B2,A2)-2)

That workd nearly perfectly. The only problem is that some of the initial cells have 9 total characters (see: Q45678989) while others have 8 total characters (see: U1234567). I need the Number column to only return 8 total characters, cutting Q45678989 down to Q4567898 and keeping U1234567 as U1234567.

 

[Rick Rothstein]

Hi Rick,

Can you please explain this part "|",REPT(" ",99)),99) in the formula?

I have seen this being used many times in text extraction formulas. But cant figure out exact thing it does.

Thanks in advance!!

Jai

Consider this example (and note instead of using 99, I used 9 to make the text small enough to print, but the reasoning as to why it works is the same)...

=TRIM(RIGHT(SUBSTITUTE("one|two|three","|",REPT(" ",9)),9))

The REPT function produces a text string of 9 spaces and the SUBSTITUTE function substitutes them for each pipe symbol (|) in the text. This results in...

"one         two         three"

(without the quotes, of course) where there are 9 spaces between the words. The key to the number (9 here, 99 in my original formula) is that it must be equal to or greater than the number of characters in all the text following the last pipe symbol (based on your examples, I judged 99 would do that). Okay, now take the right-most 9 characters (again, this number must be equal to or greater than the number of character following the last pipe symbol). This yields...

"    three"

(again, without the quotes) four spaces followed by the ending text... the TRIM function handles removing those leading space characters to leave you with just the text that followed the last pipe symbol.

 

[Rick Rothstein]

That workd nearly perfectly. The only problem is that some of the initial cells have 9 total characters (see: Q45678989) while others have 8 total characters (see: U1234567). I need the Number column to only return 8 total characters, cutting Q45678989 down to Q4567898 and keeping U1234567 as U1234567.

Just wrap the formula with a LEFT function call specifying the number of characters you want...

B2: =LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ")-1),"|",REPT(" ",99)),99)),8)

 

[jai9]

Hi Rick,

Thanks a lot for the explanation.

Jai

 

[zrall]

That makes sense. I was still trying to decipher all of the steps in the formula. Everything works perfectly now. Much appreciated and you are truly great at excel!