extract data from column based on last character [SOLVED]

[IKHAN]

Need help with A formula or macro extracting data from column A


.Extract matching data from Column A with last character "B" and paste in column B and text string needs to be added.


EXAMPLE :


column A

APPLE

APPLEB

ORANGE

ORANGEB

GRAPES

choc

rose

pot

roseb


Output reqd.in Column B


APPLE - prime

APPLEB - second

ORANGE - prime

ORANGEB - second

rose - prime

roseb- second

 

[shrivallabha]

In cell B1

=IF(RIGHT(A1)="B","second","prime")

and copy down.

 

[Faseeh]

Hi IKhan,


With your data in Column A, Try this:

=IF(RIGHT(A1,1)<>"B",A1&" - Prime",A1&" - Second")

..and drag down the column.


@Shri, Just 55 Seconds shorter then you :D


Regards,

 

[IKHAN]

Thanks for prompt reply guys....@ faseeh formula ,its adding (prime ,second) even for text not matched.


in my example above :


Marking as PRIME for


grapes

choc

pot


need to match only for same fruit.

 

[Faseeh]

...means only if the fruit is present twice and it has been appended to it then second and the first one as prime??

 

[IKHAN]

Correct ...have 2 entries in same column and one of them is appended with "B", first needs to be prime and appended needs to be second

 

[SirJB7]

Hi, IKHAN!


If you want a complex formula try this:

=IZQUIERDA(A2;LARGO(A2)-SIGNO(DERECHA(A2;1)="B"))&" - "&ELEGIR(SIGNO(DERECHA(A2;1)="B")+1;"prime";"second") -----> in english: =LEFT(A2,LEN(A2)-SIGN(RIGHT(A2,1)="B"))&" - "&CHOOSE(SIGN(RIGHT(A2,1)="B")+1,"prime","second")


But it's nice... I liked it.


Regards!

 

[NARAYANK991]

Hi Khan ,


Try this :


=IF(RIGHT(A1,1)<>"B",IF(ISNA(MATCH(A1&"B",$A$1:$A$9,0)),"","Prime"),"Second")


I have assumed your data is in the range A1:A9.


The formula is to be entered as an array formula , using CTRL SHIFT ENTER.


This formula is not going to extract only the Prime and Second entries from your data ; it will only label them Prime or Second.


Narayan

 

[shrivallabha]

Does this work:

=IF(COUNTIF(A:A,A1&"B")>0,A1&"-prime",IF(COUNTIF(A:A,LEFT(A1,LEN(A1)-1))>0,A1&"-second",""))

 

[IKHAN]

aha.. thanks for all your help guys... @ shri ...your formula works and gives me the output needed..cheers