Excel, custom format
 

I have data entered like this :

AA-BB-999-xxxxx-zz-Abcdefghij Klmno, PRSTUVW
AA-BB-888-zzzzz-zz-Abcdef Ghijk, LMNOPRSTUV

When i enter the data in A1, A2 cell, and in B1, B2 i want to have only:

AA-BB-999
AA-BB-888

After that in C1, C2 i want to have:

KK-AA-BB-999
KK-AA-BB-888


How to do it? Especially if those fields are formulas. Thanks!

Excel, custom format
 

If the portion to be extracted is of constant length then apply the below
formula in cell B1.
=LEFT(A1,9)

If this portion is of variable length then use the below formula instead
=LEFT(A1,FIND("|",SUBSTITUTE(A1,"-","|",3))-1)

In cell C1 apply the below formula and copydown as required
="KK-"&B1


--
Jacob (MVP - Excel)


"domyrat" wrote:

I have data entered like this :

AA-BB-999-xxxxx-zz-Abcdefghij Klmno, PRSTUVW
AA-BB-888-zzzzz-zz-Abcdef Ghijk, LMNOPRSTUV

When i enter the data in A1, A2 cell, and in B1, B2 i want to have only:

AA-BB-999
AA-BB-888

After that in C1, C2 i want to have:

KK-AA-BB-999
KK-AA-BB-888


How to do it? Especially if those fields are formulas. Thanks!

Excel, custom format
 

Thank you very much! It works i think.

Can you just explain this code for me to understand what you did there, so i
can reuse it later:

If this portion is of variable length then use the below formula instead
=LEFT(A1,FIND("|",SUBSTITUTE(A1,"-","|",3))-1)


"Jacob Skaria" wrote:

If the portion to be extracted is of constant length then apply the below
formula in cell B1.
=LEFT(A1,9)

If this portion is of variable length then use the below formula instead
=LEFT(A1,FIND("|",SUBSTITUTE(A1,"-","|",3))-1)

In cell C1 apply the below formula and copydown as required
="KK-"&B1


--
Jacob (MVP - Excel)


"domyrat" wrote:

I have data entered like this :

AA-BB-999-xxxxx-zz-Abcdefghij Klmno, PRSTUVW
AA-BB-888-zzzzz-zz-Abcdef Ghijk, LMNOPRSTUV

When i enter the data in A1, A2 cell, and in B1, B2 i want to have only:

AA-BB-999
AA-BB-888

After that in C1, C2 i want to have:

KK-AA-BB-999
KK-AA-BB-888


How to do it? Especially if those fields are formulas. Thanks!

Excel, custom format
 

There is one more problem, that needs help.

I got data:

AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9, PRSTUVW
AA-BB-888-zzzzz-zz-Abcdef Ghijk 8, LMNOPRSTUV

In B1 and C1, B2 and C2 i need this:

B1: Abcdefghij Klmno 9
C2:PRSTUVW

B2: Abcdef Ghijk 8
C2:LMNOPRSTUV

They are not same length.

Excel, custom format
 

there can be data entered this way also:

AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9-H, PRSTUVW
AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9-2, PRSTUVW

"domyrat" wrote:

There is one more problem, that needs help.

I got data:

AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9, PRSTUVW
AA-BB-888-zzzzz-zz-Abcdef Ghijk 8, LMNOPRSTUV

In B1 and C1, B2 and C2 i need this:

B1: Abcdefghij Klmno 9
C2:PRSTUVW

B2: Abcdef Ghijk 8
C2:LMNOPRSTUV

They are not same length.

Excel, custom format
 

Try the below formulas with the entry in cell A1

=LEFT(MID(A1,20,255),FIND(",",MID(A1,20,255))-1)
=TRIM(MID(A1,FIND(",",A1)+1,255))


--
Jacob (MVP - Excel)


"domyrat" wrote:

there can be data entered this way also:

AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9-H, PRSTUVW
AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9-2, PRSTUVW

"domyrat" wrote:

There is one more problem, that needs help.

I got data:

AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9, PRSTUVW
AA-BB-888-zzzzz-zz-Abcdef Ghijk 8, LMNOPRSTUV

In B1 and C1, B2 and C2 i need this:

B1: Abcdefghij Klmno 9
C2:PRSTUVW

B2: Abcdef Ghijk 8
C2:LMNOPRSTUV

They are not same length.