Look for values in a string

[AndreaBruneo]

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 :))

[#2]

@AndreaBruneo 

Could you please indicate on which version of Excel you are and what exactly shall be returned for each row in your sample.

[#3]

@AndreaBruneo 

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

[#4]

Hi Sergei, thank you for answering.

Excel version 2107

The returned value should be the value in D2 when the formula finds C2 or D3 when finds C3 ( and so on) in the string in column A:A

[#5]

@AndreaBruneo 

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?