Extract Names from the Cell

[VKKT]

Hi,
Greetings:

I have attached an excel sheet which are exported from a PDF file in which all the data is coming one cell. I want to extract only the names of the staff and the Staff No. (ABC407...) in this cell to another 2 separate cells. I have a separate table of the staffs and No. which are included in this list.

Please advise how this is possible.

Regards,
VKKT

[HansV]

In E2:
=TRIM(MID(A2,FIND("Employee",A2)+9,60))
In F2:
=TRIM(MID(A2,FIND("Employee",A2)+69,60))
Fill down.

Book1.xlsx

[VKKT]

Oh! great Mr. Hans. Thanks

Just for my knowledge could be please tell me how you got this 69, 60 etc. I don't understand......

Regards,
VKKT

[HansV]

I estimated that the name would be in the first 60 character after the word Employee, and the number in the 60 characters after that.

[VKKT]

Thanks Hans for your support...…….
regards,
VKKT

[VKKT]

Greetings!
Happy New Year to All.

Is the above possible via VLOOKUP also, as I have the employee's name and IDs in a separate sheet?

Regards,
VKKT

[HansV]

Could you provide a sample workbook?

[snb]

Alternative:

Code: Select all

=TRIM(LEFT(RIGHT(A2;134);20))

Code: Select all

=LEFT(TRIM(RIGHT(A2;185));7)

[VKKT]

Greetings,

Hi Mr. Hans, thanks for your response,

Attached a sample workbook.

Regards,
VKKT

[HansV]

Do you mean that you want to use VLOOKUP to get the name, given the ID? That would be (in F2)

=TRIM(MID(VLOOKUP("*"&E2&"*",A:A,1,FALSE),FIND("Employee",VLOOKUP("*"&E2&"*",A:A,1,FALSE))+9,60))

If you want to use VLOOKUP to get both ID and name, I don't understand.

[snb]

Never use merged cells in Excel.