What is the formula to extract characters to the right and left of the last (:) character?

[jcronk]

I have a cell of text that is separated by a colon ) character. The amount of text in the cell and the amount of colon characters varies. I need to extract all data to the left of the last colon character into a cell and all data to the right of the last colon character into another cell.

My source data in column A...

2 Enterprise: Corporate
3 Enterprise: Corporate: Corporate Business
4 Enterprise: Corporate: Corporate Business: Appropriations Request
5 Enterprise: Corporate: Corporate Business: Appropriations Request: Corporate
6 Enterprise: Corporate: Corporate Business: Appropriations Request: General
7 Enterprise: Corporate: Corporate Business: Corporate Facilities

Desired output in column B...


2 Enterprise
3 Enterprise: Corporate
4 Enterprise: Corporate: Corporate Business
5 Enterprise: Corporate: Corporate Business: Appropriations Request
6 Enterprise: Corporate: Corporate Business: Appropriations Request
7 Enterprise: Corporate: Corporate Business

Desired output in column C...


2 Corporate
3 Corporate Business
4 Appropriations Request
5 Corporate
6 General
7 Corporate Facilities


Any assistance greatly appreciated,

JC

 

[Ron Coderre]

With your sample data in A2:A7...assuming the numeric prefixes represent row numbers

This regular formula returns the text to the left of the last colon
B2: =TRIM(LEFT(A2,FIND("|",SUBSTITUTE(A2,":","|",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))))-1))

and this regular formula returns the text to the right of the last colon
C2: =TRIM(SUBSTITUTE(A2,B2&":",""))

Copy both of those formulas down through Row_7

Is that something you can work with?

 

[markmzz]

Another way:

B2-> =IFERROR(MID(A2,1,LOOKUP(9^9,FIND(":",A2,ROW($1:$999)))-1),A2)

C2-> =IFERROR(MID(A2,LOOKUP(9^9,FIND(":",A2,ROW($1:$999)))+2,999),"")

Markmzz

 

[Pup Denab]

HI jconk
Welcome to the forum, excel so many way to do the same thing , here's another way
Just fill this formula to cover your data


Cell B2
=LEFT(A1,LEN(A1)-LEN(C1)-2)
Cell C2
=TRIM(IF(LEN(A2)>66,MID(A2,FIND(":",A2,43)+1,20),IF(OR(LEN(A2)=65,LEN(A2)=63),MID(A2,FIND(":",A2,42)+1,43),IF(LEN(A2)=41,MID(A2,FIND(":",A2,20)+1,19),IF(LEN(A2)=21,MID(A2,FIND(":",A2,10)+1,18),"")))))


Regards
Pup

 

[Rick Rothstein]

Another way...

B2: =LEFT(A2,LEN(A2)-LEN(C2)-2)

C2: =TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",500)),500))

 

[jcronk]

Thank you Ron...this works nicely!

Appreciate your assistance.

JC

 

[jcronk]

Appreciate the assistance...thanks for sharing your knowledge! JC