Formula Help, only want numerical data after #

[CDICKENS]

I am trying to parse out some data in a cell and just return the number that is after the # .

Here is some sample data


Adderley (# 18876)
American Limoges/Sebring (# 18877)
Aynsley (# 2793)
Barker Bros, Sampson Smith (# 2794)
Belleek (# 2795)
Bing & Grondahl (# 2796)
Capodimonte (# 4218)

I want to return only the numerical data that is after the # .

For Example:

Adderley (# 18876) should return 18876
Barker Bros,Sampson Smith (# 2794) should return 2794

Here is the formula that I have started:

=RIGHT(B25,FIND("#",B25&" ")-1)

It ddoesn't quite do what I want, here are the results of that formula on the sample data:


(# 18876)
Limoges/Sebring (# 18877)
(# 2793)
Bros, Sampson Smith (# 2794)
(# 2795)
Grondahl (# 2796)
onte (# 4218)



Thanks,

Chuck

 

[Aladin Akyurek]

If you have the morefunc add-in...

=SUBSTITUTE(WMID(A1,2,1,"#"),")","")+0

Otherwise...

=SUBSTITUTE(TRIM(MID(A1,SEARCH("#",A1)+1,LEN(A1))),")","")+0

 

[gnaga]

Aladin. Is there any worksheet function equivalent to "Instr" in VBA?

GNaga

 

[CDICKENS]

Thanks Again Aladin,

That was exactly what I needed.

Chuck

 

[Aladin Akyurek]

Aladin. Is there any worksheet function equivalent to "Instr" in VBA?

GNaga

I don't think so.

 

[gnaga]

Thanks Aladin.