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)

 

[AlanY]

try

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

or

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

 

[tyija1995]

If the format is always "Enterprise name: Person_Name"
Then you can setup a function in the VBA to split a string based on a specified delimiter.

So in this case you can split based on the colon ) and take the element after the colon, nesting with a TRIM function to get rid of the whitespace.

Try this in a VBA module within your workbook,
Function EXTRACTELEMENT(txt, n, sep)
EXTRACTELEMENT = Split(Application.Trim(txt), sep)(n-1)
End Function

Then in cell L1 enter the formula as follows:
TRIM(EXTRACTELEMENT(L1, 2, ":")
The result will be "Barnaby"

This should work when dragged down the L column with all other names given a consistent format as followed.

I got the EXTRACTELEMENT function from the VBA book for dummies - handy book! Credit to them for the function.

 

[tyija1995]

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

 

[howard]

Thanks for the help guys

 

[AlanY]

you're welcome

 

[jtakw]

Hi,

You can get it to work with the RIGHT function, but would be easier with MID:

Book1
	A	B	C
1	Enterprise name: Barnaby	Barnaby	Barnaby
Sheet612

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,FIND(":",A1)+1,99))
C1	=TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1)))

 

[AlKey]

Or try this

[B]=REPLACE(A1,1,FIND(":",A1)+1,"")[/B]

 

[howard]

Thanks for your formula's

 

[sandy666]

also you can try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, ": "), type text}})
in
    #"Extracted Text After Delimiter"[/SIZE]

Column1	Column1
Enterprise name: Barnaby	Barnaby