Join text and numbers in series

[AbelGoodwin1988]

Hello!,
I'm rather new at excel and have been watching MikeG's videos and some of MrExcels. I'm learning spreadsheets for accounting and am kind of just doing extra for funsies.

I'm trying to add numbers to a text in a series, for example:
West 1, then the next two cells to the right will say west 1.2, then over two again west 1.3. I posted what I've don'e on my spreadsheet but... obviously if I pull it over to the right it wont become 1.4.
I realize this is probably a newbie question but it's something i'm interested in learning. I've fiddled around with all the possibilities i would know and they haven't panned out.
Thank you for your help and patience! :D

Excel 2007

	B	C	D	E	F	G	H	I
12	Segment 1		Segment 1.1		Segment 1.2		Segment 1.3

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D12 =B12&"."&1 F12 =B12&"."&2 H12 =B12&"."&3 <tbody> </tbody>

<tbody>
</tbody>

 

[hurgadion]

Hi,
try the formula:

=IF(MOD(COLUMN(),2)=0;$B$1&"."&COLUMN()/2-1,"")

Best regards.

 

[njimack]

Welcome to the Board. Here's another approach...

Sheet1

*	B	C	D	E	F	G	H
12	Segment 1	*	Segment 1.1	*	Segment 1.2	*	Segment 1.3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D12 =$B12&TEXT(((COLUMN()/2)-1)/10,".0") F12 =$B12&TEXT(((COLUMN()/2)-1)/10,".0") H12 =$B12&TEXT(((COLUMN()/2)-1)/10,".0") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[kpark91]

Welcome to the forum! Hi, in this case, you need simple math to get the job done. Usually, if you want something to be done in a pattern, you must figure out the pattern first mathematically (since Excel determines the pattern mathematically). If you take a look at your example, you can convert the column name to a number where A = 1, B = 2, C = 3... So, if you were to put them in a table

1 (A)	2 (B)	3 (C)	4 (D)	5 (E)	6 (F)	7 (G)
	Segment 1		Segment 1.1		Segment 1.3

<tbody> </tbody>

You can see that you have only filled in values for even numbers. So, the first step for doing what you want is to know how to print values for only EVEN numbered columns. You can achieve this by using combination of IF, COLUMN and MODULUS formula. The COLUMN formula simply returns the column number of the reference. The MODULUS formula returns the remainder of a number you have divided by dividend So, 5 MODULUS 2 would return 1=MOD(5, 2) 6 MODULUS 2 would return 0 =MOD(6,2) 7 MODULUS 5 would return 2 =MOD(7,5) So, to print only for even numbers, you would use this formula in D1. =IF(MOD(COLUMN(D1),2)=0,B1, "") where B1 is already filled in with "Segment 1" However, when you copy over and check your formulas, you will realize the formula in F1 would look something like =IF(MOD(COLUMN(F1),2)=0,D1, "") which is something you don't want. Why? Because in D1, you're planning to have "Segment 1.1" and if you refer to D1 for "Segment 1.2", you will have to fiddle around with "Segment 1.1" to get the value you want. When you could get "Segment 1.2" much easier by using B1 unconditionally! To use B1 unconditionally in any cells with copied formula, you must use Absolute Reference. You

 

[kpark91]

Welcome to the forum!
Hi, in this case, you need simple math to get the job done.

Usually, if you want something to be done in a pattern, you must figure out the pattern first mathematically (since Excel determines the pattern mathematically).

If you take a look at your example, you can convert the column name to a number where A = 1, B = 2, C = 3...
So, if you were to put them in a table

1 (A)	2 (B)	3 (C)	4 (D)	5 (E)	6 (F)	7 (G)
	Segment 1		Segment 1.1		Segment 1.2

<tbody>
</tbody>

You can see that you have only filled in values for even numbers.

So, the first step for doing what you want is to know how to print values for only EVEN numbered columns.

PRINTING FOR EVEN COLUMN NUMBERS
You can achieve this by using combination of IF, COLUMN and MODULUS formula.
The COLUMN formula simply returns the column number of the reference.
The MODULUS formula returns the remainder of a number you have divided by dividend

So, 5 MODULUS 2 would return 1=MOD(5, 2)
6 MODULUS 2 would return 0 =MOD(6,2)
7 MODULUS 5 would return 2 =MOD(7,5)

So, to print only for even numbers, you would use this formula in D1.
=IF(MOD(COLUMN(),2)=0,B1, "")
where B1 is already filled in with "Segment 1"

However, when you copy over and check your formulas, you will realize the formula in F1 would look something like
=IF(MOD(COLUMN(),2)=0,D1, "")
which is something you don't want.
Why?
Because in D1, you're planning to have "Segment 1.1" and if you refer to D1 for "Segment 1.2", you will have to fiddle around with "Segment 1.1" to get the value you want. When you could get "Segment 1.2" much easier by using B1 unconditionally!

ABSOLUTE REFERENCE
To use B1 unconditionally in any cells with copied formula, you must use Absolute Reference.
You can achieve this by using $ sign.
So, in D1 change the formula to
=IF(MOD(COLUMN(),2)=0,$B1, "")
and copy over to other columns and check F1 again!

It should look like
=IF(MOD(COLUMN(),2)=0,$B1, "")

Because the column of B1 has been absolutely referenced, B will not change in any cells copied over.
Try putting absolute reference for row number or both {column, row} and play around with it.

APPLYING
Now that you have almost all the things you need to do for this, let's try solving it.

1) To print only for even numbered columns, in D1
=IF(MOD(COLUMN(),2)=0,"Printing message", "")

2) To add values to B1.. in D1
=IF(MOD(COLUMN(),2)=0,$B1 & "." & COLUMN()/2-1,"")

The step 2 does it all with only one new QUOTIENT formula, which is similar to MODULUS formula (both found by simple division)

Hope it helps.

 

[AbelGoodwin1988]

WOW!,
Thank you everyone for the replies!
I had no idea they'd all come so quickly. :D
I'll start tooling with all these formulas you've given me and try to figure out how they work.
Thanks, Kpark1, for the break-down!

 

[AbelGoodwin1988]

Ok, I think I get this. If you don't mind i'd like to explain it out; if no one reads this then super, but if you do and I'm understanding the formulas correctly then please let me know.
=IF(MOD(COLUMN(),2)=0,$B1&"."&COLUMN()/2-1,"")
So, If statements will give you a true or false value depending on the logical statement: MOD(COLUMN(),2)=0,
Since i'm using every other column (which is even) I'll consistently get a true value unless the formula is an odd column where the IF formula would give me "" which is just a blank space.
When i'm in an even column a true value will be returned, which is;
$B1&&"."&COLUMN()/2-1
This will reproduce the text from the referenced column B! followed by a "." then a number represented by the column the formula is in divided by two then less one.

It seems this would work starting off in the two column... If I were to start off with my "segment 1" in the four column would I just need to just COLUMN()/2-1 to COLUMN()/2-3?

Seems logical. I'm going to go test it!

njimack, i'm going to try and figure out your formula next, thank you!