Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Join Text Based on Value of Left Digits

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Join Text Based on Value of Left Digits

    Hi guys

    I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!!

    The formula I am trying to do is something like this...

    In column S:S
    IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1"
    IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2"
    IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3"
    IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"

    Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.

    Thanks in advance for any help.

    Laz

  • #2
    Re: Concatenate With Left In A Macro

    Originally posted by paul_laz
    Hi guys

    I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!!

    The formula I am trying to do is something like this...

    In column S:S
    IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1"
    IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2"
    IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3"
    IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"

    Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.

    Thanks in advance for any help.

    Laz
    Does the attached help with the formula ?

    Greg.
    Attached Files
    Templates & Calculators

    Comment


    • #3
      Re: Concatenate With Left In A Macro

      Try this formula;

      =IF(--LEFT(R2,2)>12,"",CHOOSE(MATCH(--LEFT(R2,2),{1,4,7,10},1),Q2&"/"&"Q1",Q2&"/"&"Q2",Q2&"/"&"Q3",Q2&"/"&"Q4"))

      Comment


      • #4
        Re: Join Text Based on Value of Left Digits

        Thanks guys!!

        The date fields I am referencing are created earlier my macro so the format stays in a normal dd/mm/yy format, but I think I can get round it by using MID(3,2) rather than LEFT in your example, so thank you very much!!

        Laz

        Comment


        • #5
          Re: Join Text Based on Value of Left Digits

          No didn't work!!! Maybe because LEFT and MID only work on text - not date formats?!!

          Any ideas how I can round this?

          Comment


          • #6
            Re: Join Text Based on Value of Left Digits

            Hi,

            =YEAR(R2)&"/Q"&LOOKUP(MONTH(R2),{1,4,7,10},{1,2,3,4})

            HTH
            Kris

            ExcelFox

            Comment


            • #7
              Re: Join Text Based on Value of Left Digits

              Perfect!!

              And I even managed to get it into my macro!!

              Thank you

              Comment


              • #8
                Re: Join Text Based on Value of Left Digits

                Originally posted by paul_laz
                Perfect!!

                And I even managed to get it into my macro!!

                Thank you
                You are welcome!!

                Keep EXCELing!!
                Kris

                ExcelFox

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X