CODEWITHSUNDEEP
excelstringexcel-formulaformulaextract
gaus shaikh
gaus shaikh

Reputation: 1

Excel formula to extract text from mid

Here, I am extracting middle name from text using below formula

=MID(I2, SEARCH("-",I2) + 1, SEARCH("-",I2,SEARCH("-",I2)+1) - SEARCH("-",I2) - 1)

So from name "Goapta-Saaririka-Aaasshok", I can pull the "Saaririka" but I have a problem if I have only first name and middle name and not last name. Above formula is not working to pull the middle name from "Basrsaaggade-Aamimit"

Please assist as how can I pull Mid name even if there is no last name.

Upvotes: 0

Views: 252

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6216

No need to test for the number of dashes.

Assuming your data starts in A1. Put this in B1: =TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",255)),256,256))

Drag down.

It replaces every dash with 255 spaces, then it chops 256 chars from 256 chars in which guarantees the inclusion of the second element, then it trims off the excess spaces.

If you wish to do this with a UDF in VBA instead you can split the string into an array using "-" as the delimeter and take the second element.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

Test for the number of dashes. For data in column A:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,MID(A1, SEARCH("-",A1) + 1, SEARCH("-",A1,SEARCH("-",A1)+1) - SEARCH("-",A1) - 1),MID(A1,FIND("-",A1)+1,99))

enter image description here

If there are 2 dashes, take the middle string
if there is 1 dash, take the last string.

(with Excel 365 this can be made much shorter)

Upvotes: 1

Related Questions