Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Join Text Based on Value of Left Digits

  1. #1
    Join Date
    22nd January 2007
    Location
    UK
    Posts
    23

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    30th November 2005
    Location
    England
    Posts
    131

    Re: Concatenate With Left In A Macro

    Quote 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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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"))

  4. #4
    Join Date
    22nd January 2007
    Location
    UK
    Posts
    23

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd January 2007
    Location
    UK
    Posts
    23

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

  7. #7
    Join Date
    22nd January 2007
    Location
    UK
    Posts
    23

    Re: Join Text Based on Value of Left Digits

    Perfect!!

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

    Thank you

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Join Text Based on Value of Left Digits

    Quote Originally Posted by paul_laz
    Perfect!!

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

    Thank you
    You are welcome!!

    Keep EXCELing!!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Left Outer Join with more than two tables
    By klxracer in forum Excel and/or SQL Help
    Replies: 3
    Last Post: March 3rd, 2013, 11:53
  2. Replies: 7
    Last Post: April 5th, 2009, 10:39
  3. Left Join Two Tables From Sql
    By patto13 in forum Excel and/or SQL Help
    Replies: 2
    Last Post: March 3rd, 2008, 14:37
  4. Left Join With Additional Parameter In Query
    By churchillwayne in forum Excel and/or Access Help
    Replies: 7
    Last Post: August 21st, 2007, 01:22
  5. Join Number and Text Based on Criteria
    By tallkoolone in forum Excel General
    Replies: 8
    Last Post: August 16th, 2006, 08:37

Bookmarks

Posting Permissions

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