Blogger
Share this content
0
5
123312

How do a insert data within text in Excel please?

How do I insert data from a particular cell into the middle of text please?
I am typing in 'the issued share capital is "&DATA!G15" ordinary shares of £1 each'
Cell G15 of the worksheet named 'Data' holds the number '100', being the total issued shares capital.
I'm getting an error message, so obviously (or not so obviously to me!) I'm doing something wrong.
Is it because I'm entering this longhand, and maybe I should be using something from the toolbar?
Please can someone help?
Thanks.
David Evans

Replies

Please login or register to join the discussion.

Try also the 'Concatenate' function
Try '=Concatenate("Text",data ref.,"Text")'. The last text is optional; just to show that you can put some before and after the data. I guess youi can also have multiple data refs. as well but I haven't tried it.

Thanks (0)

TEXT
You need to add the TEXT function into your formula as follows:

="the issued share capital is " & TEXT(DATA!G15,"0") & " ordinary shares of £1 each."

This converts a number into a text string representing that number, with the bit after the comma being the format you want it returned as. This could be "£#,##0.00" if you wanted to show the £ sign, the , thousand separator and the pence. The & function will only join text strings, so you need to convert it before joining it.

Thanks (0)

One problem and three solutions!
Thank you Matthew, Andrew and Martin.
All the answers work so problem solved.
Looking at Martin's solution, in this particular instance the 'TEXT' part does not make a difference, but I can see that it will do as I progress to inserting different types of data, so thank you for the explanation.
I have been thinking of buying 'The Accountants Guide to Excel' by James Fulford but note there is an original and advanced version.
Does anyone know these books, and if so is one recommended more than the other?

Thanks (0)

Missing a "&" sign
Try the following formula:

="the issued share capital is "&Data!G15&" shares of £1 each."

You need to use a & sign to join each text/cell value. Don't know if your posting was exact syntax but you also need to use " instead of ' to capture text.

Hope this helps.

Thanks (0)

What you are doing wrong
You are entering the text as text. This won't work, since what you are using is really a formula which combines text elements.

The correct input is:

="the issued share capital is " & DATA!G15 & " ordinary shares of £1 each."

In that formula, the two partial sentences in quotation marks are being treated as inputs to a string equation, as is the content of cell G15. NB don't forget the space at the start of the second partial sentence, otherwise the output will read "...100ordinary..." rather than "...100 ordinary..."

Thanks (0)