Rearrange the contents of a cell using a formula

correlate
correlate used Ask the Experts™
on
Dear Experts

In excel I need to rearrange the contents of a cell using a formula ... Each cell has 3 bits of information

job, company, dates (i.e. CEO, Microsoft, 2015-2018)

These are all all separated by ", " (Comma space) & I need the result to read

company, job, dates (i.e. Microsoft, CEO, 2015-2018)

Can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor, Independant Technology Professional
Commented:
It would be a combination of MID functions and FINDs to locate the ", " text.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Assuming your string is in A2, then try this...
In B2
=TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",LEN($A2))),COLUMNS($B2:B2)*LEN($A2),LEN($A2)))

Open in new window

and then copy to right.

Author

Commented:
That gave me the lead to how to solve it, so thanks for that - in the end the formula was ...

=TRIM(MID(SUBSTITUTE(","&AB2,",",REPT(" ",LEN(AB2))),LEN(AB2)*(LEN(AB2)-LEN(SUBSTITUTE(AB2,",",""))),LEN(AB2))) &" - "&LEFT(AB2,FIND(",",AB2,1)-1)&IF(RIGHT(AB2,1)="-"," ("&RIGHT(AB2,5)&"Present"," ("&RIGHT(AB2,9))&")"
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
That gave me the lead to how to solve it, so thanks for that - in the end the formula was ...

=TRIM(MID(SUBSTITUTE(","&AB2,",",REPT(" ",LEN(AB2))),LEN(AB2)*(LEN(AB2)-LEN(SUBSTITUTE(AB2,",",""))),LEN(AB2))) &" - "&LEFT(AB2,FIND(",",AB2,1)-1)&IF(RIGHT(AB2,1)="-"," ("&RIGHT(AB2,5)&"Present"," ("&RIGHT(AB2,9))&")"

Author

Commented:
That gave me the lead to how to solve it, so thanks for that - in the end the formula was ...

=TRIM(MID(SUBSTITUTE(","&AB2,",",REPT(" ",LEN(AB2))),LEN(AB2)*(LEN(AB2)-LEN(SUBSTITUTE(AB2,",",""))),LEN(AB2))) &" - "&LEFT(AB2,FIND(",",AB2,1)-1)&IF(RIGHT(AB2,1)="-"," ("&RIGHT(AB2,5)&"Present"," ("&RIGHT(AB2,9))&")"

Author

Commented:
That gave me the lead to how to solve it, so thanks for that - in the end the formula was ...

=TRIM(MID(SUBSTITUTE(","&AB2,",",REPT(" ",LEN(AB2))),LEN(AB2)*(LEN(AB2)-LEN(SUBSTITUTE(AB2,",",""))),LEN(AB2))) &" - "&LEFT(AB2,FIND(",",AB2,1)-1)&IF(RIGHT(AB2,1)="-"," ("&RIGHT(AB2,5)&"Present"," ("&RIGHT(AB2,9))&")"
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Sorry to say but you should learn how to ask a question and accept an answer.
We provide solution based on the description and sample data you provide and the formula I suggested produced the desired output based on the sample data you provided in the description.

Formula.jpg

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial