Need formula that will read the first number only

[serge]

Hope someone can help me.

I need couple formulas, the reason I ask them in the same tread is because they work for the same sheet and it would be harder to understand if I would ask separately !!!.

First one :
I have in

A1: 14
B1: 26
C1: 3

or

A1: 2
B1: 17
C1: 8

and I need in D1 a formula that will put those 3 numbers together with 1 space in between ? Which would give.

D1: 14 26 3
D1: 2 17 8

Then here is my display.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			1	2	3	4	5	6	7	8	9	10	11	12
2
3
4		1	7	2	9	3	3	1	9	10	8	1	4	5
5		2	1	7	2	9	9	3	1	9	10	8	1	4
6		3	2	1	7	2	2	9	3	1	9	10	8	1
7		4	3	3	1	7	7	2	2	3	1	9	10	8
8		5	4	4	3	1	1	7	7	2	3	3	9	10
9		6	5	5	4	4	4	4	4	7	2	2	3	9
10		7	6	6	5	5	5	5	5	4	7	7	2	3
11		8	8	8	6	6	6	6	6	5	4	4	7	2
12		9	9	9	8	8	8	8	8	6	5	5	5	7
13		10	10	10	10	10	10	10	10	8	6	6	6	6
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5	1. / Formula is =AND(C4<>0,C$4<=D$4,C4=D$4)	Abc

Second question.

Can the formulas for this set up be change to work exactly the same way BUT the formula would work using the first number ONLY from that whole number ???

Here with this example I have in A1: 14 B1: 26 C1: 3 which become in D1: 14 26 3, I need the formula in C5 to work with a number show in D1 as example and just using the number 14 and for the second example the number 2.

And the same with the CF, the formula will use the first number only too ?

I really hope that is possible, that would help me so much and have all my data display in one single sheet.

That look like a good challenge to me, but you guys are genius.

Thank you in advance.

Serge.

 

[JackDanIce]

For first part use:

=A1 & " " & B1 & " " & C1

 

[drl44]

for the first part =A1&" "&B1&" "&C1
for the second =LEFT(D1,FIND(" ",D1))

i hope this helps

 

[serge]

Thank you JackDanIce,

That works for the first part.

 

[serge]

drl44, thank for the help but,

I need this formula :

=IF(C$4="",0,IF(COUNTIF(C$4:C4,B4),B5,B4))

which is in C5 to be change to work with the first number of 14 26 3 which is in one cell ( for this example ).
Thank you.

 

[drl44]

I need some clarification so what I think you are asking is with your table you want in cell C5 you want the values of C5,D5,E5 est.. with spaces in between each value?

 

[serge]

drl44,

No that I have it, what I need is for the formula in C5 to calculate what this cell is housing but only with the first number.

 

[drl44]

sorry but i dont understand do you want to get the the values of the table with out the table?

 

[serge]

I just want to say that I'm very sorry.

I just realized that, what I'm asking would work only at 66% of what I actually need, so you don't need to answer to this post anymore.

I want to apologized for my mistake.