extract name after :

[howard]

I have tried to set up a formula to extract the name to the right of : . in this instance I want Barnaby extracted

I can get it to work, but if the name changes, I have to amend my formula



Your assistance is most appreciated

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Enterprise name: Barnaby											Barnaby
Sheet1

Cell Formulas
Range	Formula
L1	=RIGHT(A1,SEARCH(":",A1)-8)

 

[AlKey]

Or

=mid(a1,find(":",a1)+2,1000)

 

[howard]

Thanks for the help. I am still trying to get to grips with Power Query. Are there any good tutorials or books you can recommend ?

 

[howard]

Thanks for the help

 

[sandy666]

I am still trying to get to grips with Power Query. Are there any good tutorials or books you can recommend ?

• Excel Power Query (Get & Transform) Tutorial for Beginners
• Microsoft Power Query for Excel Help
• Getting Started with Get & Transform in Excel

and some more, use Google

edit:
I forgot about book: PowerQuery M reference [free]

 

[TotallyConfused]

Sorry, the formula should reference A1 not L1.
TRIM(EXTRACTELEMENT(A1, 2, ":")

If I may make a comment here, as this is written, I believe you will get an error message because you have an uneven number of opening and closing parenthesis. It should be as follows:

[SIZE=5][COLOR=black][FONT=Calibri]=Trim(ExtractElement(A1,2,":")[/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri])[/FONT][/COLOR]
[/SIZE]

I'd like to suggest another version of the same method of extracting the text following the ':'. I believe this will be easier to remember and less typing if this function is called many times in different places.

[SIZE=5][COLOR=black][FONT=Calibri]Function ExtractElement(txt, n, sep)[/FONT][/COLOR]
[/SIZE]
[SIZE=5][COLOR=black][FONT=Calibri]   ExtractElement = Trim(Split(Application.Trim(txt), sep)(n - 1))[/FONT][/COLOR][/SIZE]
[SIZE=5][COLOR=black][FONT=Calibri]End Function[/FONT][/COLOR][/SIZE]

Now place this in the cell and copy down as desired.

[SIZE=5][COLOR=black][FONT=Calibri]=ExtractElement(A1,2,":")[/FONT][/COLOR]
[/SIZE]

TotallyConfused

 

[TotallyConfused]

Refering to my post #15 , I stand corrected. The function as it was originally written in posting #3 is correct, and does not need to be changed in any way. The formula that is placed in the cell does not need the 'TRIM' command. Enter it as:

[COLOR=black][FONT=Arial]=ExtractElement(A1,2,":")[/FONT][/COLOR]

I am sorry if my error has caused confusion for anyone.

TotallyConfused (yes, as you can see, I still am.)

 

[howard]

Thanks for the info Sandy

 

[sandy666]

You are welcome
Have a nice day

 

[tyija1995]

You're right about the parenthesis, I only had 1 closing but 2 opening so it would indeed generate an error, good spot! The additional trim in the VBA code is a nice touch too, I am most certainly not totallyconfused :D