'Excel cut Initials out of a Profilename and show it as full name in another Row
I have a datasheet in Excel with 154 Columns. A is with a profile name for example like this:
T_Data_Capture_CustomerData_(jp)
What I want to do is make a new Column with the full name of the Author from the initials that are in the profile name _(jp) (example Johnson, Paul).
Now I have multiple profiles here, with different people and their initials like: (ss),(mwp),(an) etc and I also have the Full Names of the Author and their initials as a seperate Datasheet from which I can read the data from.
Also the profile names don't all start the same, they are different in lenght, examples:
P_V8_Intersport_I_WE_IBD_AVIS_SAVE_XRange_(mi) P_DSV-DM_Fortras-Release-6_BORD128_to_ALFLAT-ALBORD_(ak) P_V4_100_Gardner_Denver_Credit_to_ALINVOICE_Part_01_Processing_Of_Data_(ss)
It would look something like this:
| Profile name | Author |
|---|---|
| T_Data_Capture_CustomerData_(jp) | Johnson, Paul |
| P_V8_Intersport_I_WE_IBD_AVIS_SAVE_XRange_(ss) | Smith, Sophie |
I just don't really know how to achieve this. Any help would be appreciated.
Solution 1:[1]
So, simple, but the first one relies on the initials being in the last 3 characters:
VLOOKUP(LEFT(RIGHT(A1,3),2),E2:F3,2,0)
If you want more characters then you can use mid() with find() to locate the ( as the start and ) as the end. Like so:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |