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
Extract Names from the Cell
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Extract Names from the Cell
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract Names from the Cell
In E2:
=TRIM(MID(A2,FIND("Employee",A2)+9,60))
In F2:
=TRIM(MID(A2,FIND("Employee",A2)+69,60))
Fill down.
=TRIM(MID(A2,FIND("Employee",A2)+9,60))
In F2:
=TRIM(MID(A2,FIND("Employee",A2)+69,60))
Fill down.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Extract Names from the Cell
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
Just for my knowledge could be please tell me how you got this 69, 60 etc. I don't understand......
Regards,
VKKT
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract Names from the Cell
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Extract Names from the Cell
Thanks Hans for your support...…….
regards,
VKKT
regards,
VKKT
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Extract Names from the Cell
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
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
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Extract Names from the Cell
Alternative:
Code: Select all
=TRIM(LEFT(RIGHT(A2;134);20))
Code: Select all
=LEFT(TRIM(RIGHT(A2;185));7)
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Extract Names from the Cell
Greetings,
Hi Mr. Hans, thanks for your response,
Attached a sample workbook.
Regards,
VKKT
Hi Mr. Hans, thanks for your response,
Attached a sample workbook.
Regards,
VKKT
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract Names from the Cell
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.
=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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Extract Names from the Cell
Never use merged cells in Excel.
You do not have the required permissions to view the files attached to this post.