VBA Code to split cell's text data

[Liz_Biz]

Hey Team,

I have a macro that give file location in Column F. I want to split cell's data of column F between last two backslash. First splitted data should go to Column E and last to column A but without .xlsx. Location format would be same for all entire column F.

Also column A is a name and there is a email database in sheet "Email ID data" . We also need to identify email ID for each of entry in column A.

I have attached sample sheet. Please look into this for reference. Please help on this.

Thanks,
Liz

[buran]

Hi,
Here is macro that
split value in column F
populate column A
Populate column E
populate B with VLOOKUP formula that retrieves data from the other sheet

Please Login or Register  to view this content.

[Richard Buttrey]

See attached. You don't need macros. Columns A & E contain formulae to do the job for you.

And of course a standard VLOOKUP will get you the email address onto the EMail ID Data sheet - or indeed an Advanced Data Filter.

[Liz_Biz]

Thanks Buran for quick response !!

Is it possible to split path like "C:\Documents and Settings\liz\Desktop\INST\UID_August_Baker,Edward D.xlsx" into two parts?
Name (Column A) "Baker,Edward D" and Subject (Column E) "UID_August". Rest condition would be same.

Also we can't use vlookup as this fails once it search same value again.

[Liz_Biz]

Thanks Rich !!

You solution is pretty nice. But my colleagues are quite lazy. Its pretty tough for them to learn how to use formula.

Thanks again for your solution.

[buran]

Is it possible to split path like "C:\Documents and Settings\liz\Desktop\INST\UID_August_Baker,Edward D.xlsx" into two parts?
Name (Column A) "Baker,Edward D" and Subject (Column E) "UID_August". Rest condition would be same.

Yes, it's possible but you should specify where to split (e.g. at last underscore)

Also we can't use vlookup as this fails once it search same value again.

I don't understand waht you mean with that. Can you explain further. You can always convert formula to value...

[Liz_Biz]

Yes. It's last underscore.

Sorry !! vlookup is working fine. Can we remove this vlookup formula and past only value?

[buran]

test this if it works

Please Login or Register  to view this content.

[Liz_Biz]

Buran,

I am getting underscore at the end and start of split data (column A & E). Also vlookup is not working due to underscore and .xlsx in column A.

Thanks,
Liz

[buran]

Sorry, now it should be OK

Please Login or Register  to view this content.

[Liz_Biz]

You solved it very fast!!

Thank you buran !!

[buran]

Great, don't forget to mark thread as [SOLVED]