I have following on a1
001. Andrew Fisher
(AUS)
002. Adam Kretschmer
(AUS)
003. Karen Richards
(AUS)
I need formula on b1 drag down to give me result as follows
, AUS in not only the country , the list is long enough
001. Andrew Fisher(AUS)
002. Adam Kretschmer(AUS)
003. Karen Richards(AUS)
Two-stage process:
1. In B1 copied down:
=IF(ISEVEN(ROW()),1,A1&A2)
2. Change column B to values, then remove duplicates from column B (data ribbon).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Something like:
=INDEX($A$1:$A$1000, 2*ROW(B1)-1) &
=INDEX($A$1:$A$1000, 2*ROW(B1))
IF(RIGHT(A1,5)="(Aus)","Australia is not only the country",SUBSTITUTE(MID(A1,FIND("(",A1,1)+1,SEARCH(")",A1)),")",""))
Thank you all.
Last edited by AliGW; 11-08-2017 at 03:02 AM. Reason: Personal comment removed.
May be
Row\Col 001. Andrew Fisher
(AUS)
002. Adam Kretschmer
(AUS)
003. Karen Richards
(AUS)001. Andrew Fisher (AUS) 002. Adam Kretschmer (AUS) 003. Karen Richards (AUS)
B1Formula:
Please Login or Register to view this content.
Copy down!
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks