From: domyrat on
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
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
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
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
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.
 |  Next  |  Last
Pages: 1 2
Prev: How to arrange data
Next: Rating