Excel help!

[Vipers]

Hi all, I am trying to insert a cell value into a piece of text in another cell for example if C2 is a calculation and the answer shown is 8

I want to insert it into another cell which reads "The O2% is. ", so it will read "The O2% is 8"

Cheers guys.

[illmonkey]

="The O2% is. " & C2 & "%"

[Sporky]

Not sure you need the last & "%" for the stated outcome.

The other approach that occurs (not that it's any better, mind) is:

=CONCATENATE("The O2% is ",C2)

Brain's nagging at me that there's a way to do this using a custom text format in C2, but that's not quite what was asked.

[Doofus]

Sporky said:

Not sure you need the last & "%" for the stated outcome.

The other approach that occurs (not that it's any better, mind) is:

=CONCATENATE("The O2% is ",C2)

Brain's nagging at me that there's a way to do this using a custom text format in C2, but that's not quite what was asked.

This is the thing about Excel. They created a CONCATENATE formula, and then decided that you didn't actually need to use the word CONCATENATE after all.

I'm fairly sure it's not the only example, but I can't bring any others to mind just now.

[Sporky]

There's VLOOKUP vs using INDEX & MATCH.

And INDIRECT - probably the most useful and yet godawful function in Excel.

[Vipers]

Thanks for the comments, will try suggestions when I get home.

[hyphen]

illmonkey said:

="The O2% is. " & C2 & "%"

=IF(C2="","","The O2% is. " & C2 & "%")

[MOBB]

You probably want a ROUND in there as well if you are concatenating calculated values

[Sporky]

hyphen said:

=IF(C2="","","The O2% is. " & C2 & "%")

Pah. Banana to a gun fight.

=IFERROR(IF(NOT(ISBLANK(C2)),CONCATENATE("The O2% is ",ROUND(C2, 2),"Cell C2 is blank"),"Error encountered")

Edited by Sporky on Monday 30th September 19:30

[Sporky]

MOBB said:

You probably want a ROUND in there as well if you are concatenating calculated values

Good call. I've incorporated that into my compound horror-function above.

[hyphen]

Sporky said:

Pah. Banana to a gun fight.

=IFERROR(IF(NOT(ISBLANK(C2)),CONCATENATE("The O2% is ",ROUND(C2, 2),"Cell C2 is blank"),"Error encountered")

Edited by Sporky on Monday 30th September 19:30

You know nothing, Sporky Snow

[Vipers]

Sporky said:

hyphen said:

=IF(C2="","","The O2% is. " & C2 & "%")

Pah. Banana to a gun fight.

=IFERROR(IF(NOT(ISBLANK(C2)),CONCATENATE("The O2% is ",ROUND(C2, 2),"Cell C2 is blank"),"Error encountered")

Edited by Sporky on Monday 30th September 19:30

WOW.. anyway for I wanted to do "illmonkeys" suggestion worked fine, thanks for that, and thanks to one and all.

[hyphen]

Vipers said:

Sporky said:

hyphen said:

=IF(C2="","","The O2% is. " & C2 & "%")

Pah. Banana to a gun fight.

=IFERROR(IF(NOT(ISBLANK(C2)),CONCATENATE("The O2% is ",ROUND(C2, 2),"Cell C2 is blank"),"Error encountered")

Edited by Sporky on Monday 30th September 19:30

WOW.. anyway for I wanted to do "illmonkeys" suggestion worked fine, thanks for that, and thanks to one and all.

See Sporky Padawan, much to learn you have

[Sporky]

My users are more determined to break things!

I was trying to work out how to embed all of that into a HYPERLINK function, but I see that might be a step (or five) too far...

[hyphen]

Sporky said:

My users are more determined to break things!

As long as they sign off UAT first, they can break what they want!

Much to learn

[Clockwork Cupcake]

hyphen said:

You know nothing, Sporky Snow

https://www.xkcd.com/2200/

[Sporky]

hyphen said:

As long as they sign off UAT first, they can break what they want!

Much to learn

Sadly my users are my team.

Today one of them asked for a function that woukd sort part codes alphabetically within each sub-section of the quote. Because "it would look neater". Gah.

[ruggedscotty]

Sorry to hijack....

Can anyone help here ?

An Excel sheet has a tab with a list of locations with data on many columns.....

Is there a way to make a tab that you can have a pull down list that when you select a particular location it then populates a template with different values obtained from the line with the location name on it.

just wondering.....

[CaptainSlow]

ruggedscotty said:

Sorry to hijack....

Can anyone help here ?

An Excel sheet has a tab with a list of locations with data on many columns.....

Is there a way to make a tab that you can have a pull down list that when you select a particular location it then populates a template with different values obtained from the line with the location name on it.

just wondering.....

Very easy.

just use a data validation list on the list of locations and then use numerous index matches on the template fields.

[louiebaby]

Sporky said:

Today one of them asked for a function that woukd sort part codes alphabetically within each sub-section of the quote. Because "it would look neater". Gah.

I once had a colleague ask for more rows because she'd got to the bottom of the spreadsheet.

She was on row 170 or thereabouts, and was very pleased when I rolled the mouse wheel down for her.

She's now a director of a fairly large energy consultancy.