Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Join Text Based on Value of Left Digits

1. Member
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. Senior Member
Join Date
30th November 2005
Location
England
Posts
131

## 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.

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

4. Member
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. Member
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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## 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. Member
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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## 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!!

#### Thread Information

##### Users Browsing this Thread

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

#### 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