|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 19th, 2010, 08:15 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
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! 
|
|
#2
April 19th, 2010, 08:23 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!
|
|
#3
April 19th, 2010, 08:45 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!
|
|
#4
April 19th, 2010, 08:57 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#5
April 19th, 2010, 09:00 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#6
April 19th, 2010, 12:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
