Aug 24 2021 02:54 AM
Hi everybody,
I am trying to set a formula that allows me to find some values in a string and gives back the requested values.
The column Specification contains some # followed by ID plus letters/numbers. I would need to create a new column that gives back only the #ID.
Basically the formula should do: search in column A for C2 and giveback D2, or search C3 and give back D3, and so on
could you please support with that?
many thanks :))
Aug 24 2021 06:15 AM
Could you please indicate on which version of Excel you are and what exactly shall be returned for each row in your sample.
Aug 24 2021 08:40 AM
Hi,
Simple version:
=MID(A1;FIND("#";A1);FIND(" ";A1;FIND("#";A1))-FIND("#";A1))
it assumes that #ID ends with space character.
But if your data can be more complicated think of regex dark power. You can find examples of UDF functions and how to use them on https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel...
Regards
Artur
Aug 24 2021 08:42 AM
Aug 24 2021 09:18 AM
Thank you, so you are on Excel 365.
As for sample - sorry, I still don't understand. In the sample we have 5 records in A2:A6. Formula shall search text "#IC" (C2) in above range and return in D2 same text "IC". Why? Where is no "#IC" in A2:A6.
Or, if "#IC" is not found we shall check next (in C3), also nothing, finally we find "#OT" from C3 - are sure we shall return the same string into D3?