Copy Heading number in condition

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

Attachment 29923

for question 1 only.

Potential for question 2

another possible solution for both 1 and 2 points.

PHP Code:

---

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


---

or UDF:

Code:

---

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

---

Quote:

---

Originally Posted by georgiboy

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.

Quote:

---

Originally Posted by arnelgp

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.

Attachment 29927

Quote:

---

Originally Posted by kamkwok6

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

Attachment 29927

---

I have Modified it now.
Attachment 29929

Quote:

---

Originally Posted by kamkwok6

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.

Quote:

---

Originally Posted by arnelgp

I have Modified it now.
Attachment 29929

---

Attachment 30037

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

Many thanks~