Consulting

Results 1 to 10 of 10

Thread: Copy Heading number in condition

  1. #1

    Post Copy Heading number and Create Reference with VBA

    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:
    Result.jpg

    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:

    Result2.jpg
    Attached Files Attached Files
    Last edited by kamkwok6; 07-08-2022 at 03:02 AM.

  2. #2
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    421
    Location
    for question 1 only.
    Attached Files Attached Files

  3. #3
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    814
    Location
    Potential for question 2
    Attached Files Attached Files
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  4. #4
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    421
    Location
    another possible solution for both 1 and 2 points.
    Attached Files Attached Files

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,586
    PHP Code:
    =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
    Last edited by snb; 07-08-2022 at 08:35 AM.

  6. #6
    Quote Originally Posted by georgiboy View Post
    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.

  7. #7
    Quote Originally Posted by arnelgp View Post
    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.

    螢幕截圖 2022-07-08 下午10.53.11.jpg

  8. #8
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    421
    Location
    Quote Originally Posted by kamkwok6 View Post
    I dont know why, but when I click into the cell again, it doesn't work.

    螢幕截圖 2022-07-08 下午10.53.11.jpg

    I have Modified it now.
    snap.png
    Attached Files Attached Files

  9. #9
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    814
    Location
    Quote Originally Posted by kamkwok6 View Post
    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.
    Attached Files Attached Files
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  10. #10
    Quote Originally Posted by arnelgp View Post
    I have Modified it now.
    snap.png
    Annotation 2022-08-11 100321.jpg

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

    Many thanks~

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •