Excel, custom format
in [General]
|
Prev: How to arrange data
Next: Rating
From: domyrat on 19 Apr 2010 03:15 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!
From: Jacob Skaria on 19 Apr 2010 03:23 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!
From: domyrat on 19 Apr 2010 03:45 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!
From: domyrat on 19 Apr 2010 03:57 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.
From: domyrat on 19 Apr 2010 04:00
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. |