Copy Heading number in condition

[kamkwok6]

I have two functions want to be achieved

Question 1

I know it is a solution to copy the heading number inside the bracket. as below
=VALUE(MID(A1,FIND(“(“,A1)+1,FIND(“)”,A1)-FIND(“(“,A1)-1))

However , I am looking for a VBA solution to copy heading number to other cell no matter it is contained by a bracket or not.

see attached"Question.xlsx". Thanks a lot

Q1 Result:
29921

Question 2
I have another excel showing which manually inputted the reference section number from Question 1,
I would like to ask if it is possible to convert it to its corresponding ID.
It may look like a lookup function, but multiple section reference may be included in one single cell.

see attached "Question2.xlsx". Thanks a lot
Q2 Result:

29923

[arnelgp]

for question 1 only.

[georgiboy]

Potential for question 2

[arnelgp]

another possible solution for both 1 and 2 points.

[snb]

=IF(ISERROR(FIND(".";LEFT(C2;3)));C2;MID(C2;FIND(" ";C2)+1;LEN(C2)))
or UDF:


Function F_snb(it)
F_snb = IIf(InStr(it, "."), Trim(Mid(it, Len(Split(it)(0)) + 1)), it)
End Function

[kamkwok6]

Potential for question 2

What if maintaining the section ID in column B rather move it to column A?

But anyway, You gave a great inspiration for me. Thanks a lot.

[kamkwok6]

another possible solution for both 1 and 2 points.

I dont know why, but when I click into the cell again, it doesn't work.

29927

[arnelgp]

I dont know why, but when I click into the cell again, it doesn't work.

29927


I have Modified it now.
29929

[georgiboy]

What if maintaining the section ID in column B rather move it to column A?

But anyway, You gave a great inspiration for me. Thanks a lot.

I have adjusted the attached to use index/match instead of vlookup. I moved the column out of pure laziness as I find vlookup easier to use than index/match.

[kamkwok6]

I have Modified it now.
29929

30037

its looping until the end of the sentence, How can I stop it when it have a blank space.?

Many thanks~