Formula to remove Text portion of Concatenation

[decafdave]

Hey,

Let's say A1=5
B1=Days
In C1 I have =A1&" B1"

However, I want to reference cell C1 in another formula without errors. I don't think I can use Left formula or any other that I'm aware of since cell A1 and B1 can both differ greatly in length.

Thanks!

I'm using 2010.

 

[VoG]

In a spare column

=LEFT(C1,LEN(A1))+0

But why not just use A1 in your formulas?

 

[T. Valko]

Hey,

Let's say A1=5
B1=Days
In C1 I have =A1&" B1"

However, I want to reference cell C1 in another formula without errors. I don't think I can use Left formula or any other that I'm aware of since cell A1 and B1 can both differ greatly in length.

Thanks!

I'm using 2010.

It's not real clear what you want to do.

Your formula will work better like this:

=A1&" "&B1

 

[decafdave]

It's not real clear what you want to do.

Your formula will work better like this:

=A1&" "&B1

T Valko,

I actually have that in my real formula-I should have looked at it more closely before my question.

Here is my actual C1:
=LARGE(F17:F25016,1)&" "&B16

B16 is time unit input.

voG,

Thanks for information on the =LEN formula I believe it will solve my problem.
Of course, for what I'm currently doing, I could just skip all this and use the Large formula but I really wanted to solve how to take apart a concatenation. It could prove useful in the future.

 

[Scott Huish]

BTW, you can use MAX instead of LARGE with the 1 argument.

 

[T. Valko]

T Valko,

I actually have that in my real formula-I should have looked at it more closely before my question.

Here is my actual C1:
=LARGE(F17:F25016,1)&" "&B16

B16 is time unit input.

voG,

Thanks for information on the =LEN formula I believe it will solve my problem.
Of course, for what I'm currently doing, I could just skip all this and use the Large formula but I really wanted to solve how to take apart a concatenation. It could prove useful in the future.

Ok...

Here's another one...

=SUBSTITUTE(C1," "&B16,"")

 

[T. Valko]

Ok...

Here's another one...

=SUBSTITUTE(C1," "&B16,"")

Hmmm...

What happened to the double unary minus?

The correct formula should be:

=--SUBSTITUTE(C1," "&B16,"")

 

[decafdave]

Hilarious.

Anyways, thanks for the help.