Combine Cells in Excel Without Concatenate

Good news, if you’re spelling challenged — or too lazy to type long words. You can combine cell values in Excel, without using the CONCATENATE function. Keep reading, to learn the easy way to combine cells, and add some fancy formatting to the dates and numbers.

Use the & Operator

Yes, instead of using CONCATENATE, you can use the ampersand operator — & — to combine cell values in Excel. That’s a savings of 10 characters! And in the hot weather we’re having this summer, it’s important to conserve your energy for more important things.

Even more good news — you don’t need to remember how to spell “ampersand”, and you can even use one of its less technical names — “Fancy And” or “Shift-7”.

Combine Two Cells

In the cell where you’d like to see the combined values from two other cells:

• Type an = sign, to start the formula
• Click on the first cell that you want to combine
• Type an &
• Click on the second cell that you want to combine.

In the screenshot below, the product name and amount are being combined, and the formula is:
=B2&E2

• Press Enter, to complete the formula

The values from the two cells are combined into one continuous text string, showing the product name and price.

Add a Space Between Combined Text

Your formula to combine the product name and price cells worked as advertised, but the results would look better with a space between the product name and price. To create a space, you can include a text string in the formula.

• Select the cell with the formula
• Click after the first cell reference
• Type the & operator
• Type ” ” (double quote, space, double quote)
• Type the & operator
• Press Enter to complete the revision

The revised formula is:
=B2&” “&E2

The product name and price now have a space between them.

Format Numbers in Combined Cells

When you combine text with a date or number, you can format the result by using the TEXT function. The TEXT function has two arguments — the cell reference, and the formatting. In this example, you can format the number as currency, with two decimal places.

TEXT(E2,”\$#,##0.00″)

• Select the cell with the formula
• Change the second cell reference, to include the TEXT function
• Press Enter to complete the revision

The revised formula is:
=B2&” “&TEXT(E2,”\$#,##0.00”)

The product name and price now have a space between them, and currency formatting on the number.

Help With Number Formats

If you need help with setting up the Number Format argument in the TEXT function, there are a few more examples on the Combine Cells in Excel page.

You can also format a sample cell in Excel, using the Number Format commands. Then, to see its formatting code:

• Select the formatted cell
• Press Ctrl+1, to open the Format Cells dialog box.
• On the Numbers tab, click the Custom category
• Copy the formatting from the Type box.
• Close the dialog box, and paste the formatting into the TEXT formula

Watch the Combine Cells in Excel Video

To see the steps for creating a formula to combine and format cells, please watch this short Excel video tutorial.

_____________

66 Responses

1. David says:

Debra- For many years I’ve wondered about the CONCATENATE and ampersand. I kept thinking I’d come across the answer by now, but since I haven’t, I hope someone like you could provide it.

Why would I use CONCATENATE instead of &? The & is much easier and cleaner. The only time I can think to use the function is when I’m concatenating lots and lots of cells and a comma is easier to type than shift+7.

There are two ways in which I think the CONCATENATE function could provide value, but it does neither of these things.
1) If it allowed you to use a range of cells as the argument. For example, =CONCATENATE(A1:A4) would combine the values in those four cells.
2) If it allowed also let you input a delimited between values in the range. For example, =CONCATENATE(A1:A4, “, “) would combine those four cell, but put a comma between each entry. I know you could do this with a UDF, but I wish it were part of the function itself.

Sorry for the rant, but if you could enlighten me about why CONCATENATE exists and what I could be missing, I’d really appreciate it. It doesn’t keep me up at night, but it causes me to become annoyed and confused more often than I’d like.

Thanks.

• Ken says:

I would like to know how could i concatenate text on my button that I have created on my worksheet.
For example I have Inserted a button on my worksheet and I want to concatenate text from 2 cells to my button; cells G1 and M1

2. @David, you’re not missing anything — CONCATENATE could be a useful function, if it had the features that you suggested, but it doesn’t.
I never use it, and if there’s a long list of cells to join, I just copy and paste the ampersands and delimiters within the formula.

• Cathy says:

Thank you! This worked wonderfully.

• Cathy says:

I Think concatenate can be easier than &. You can concatenate many cells by typing =concatenate( and then holding down CNTL while clicking on the cells in the sequence you want to concatenate them. Excel automatically puts the commas in and this saves typing the &.

• SteveE says:

I think you will find that ‘CNTL’ is actually ‘Ctrl’

3. Rick Rothstein (MVP - Excel) says:

@David (and others),

Perhaps this UDF (user defined function) will be of some use to you…

`Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As StringDim Cell As Range, Area As VariantIf IsMissing(Delimiter) Then Delimiter = “”For Each Area In CellRangesIf TypeName(Area) = “Range” ThenFor Each Cell In AreaIf Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.ValueNextElseConCat = ConCat & Delimiter & AreaEnd IfNextConCat = Mid(ConCat, Len(Delimiter) + 1)End Function`

If you have never installed a UDF, the procedure is simple… press Alt+F11 to go into the VB editor, click Insert/Module from its menu bar and then Copy/Paste the above code into the code window that opened up. That’s it. Go back to your worksheet put some text in, say A1, A2, A3, C1, D1, D2 and then type this formula into an unused cell…

=ConCat(“-“,A1:A3,C1,”HELLO”,D1:D2)

and the contents of those cells (and the word “HELLO”) will be concantenated together, in the order shown, with a dash between them. The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string (“”) as the first argument or simply omit it (but still use the comma as a place-holder in order to delineate the argument position). So, you could concatenate my above example cells and text, but with no delimiter between them, either like this…

=ConCat(“”,A1:A3,C1,”HELLO”,D1:D2)

or like this (note the leading comma)…

=ConCat(,A1:A3,C1,”HELLO”,D1:D2)

your choice. If you install the UDF into a workbooks, then the function will travel with the file if you distribute it to others. If you find this UDF useful and want it available for use on any worksheets that only YOU will work on, just install it in your personal.xls file… just remember, though, if you install it to your personal.xls file and use if from there, then the function will NOT travel with any worksheets you distribute to others (meaning cells using ConCat will produce a #NAME! error on their computers) unless, of course, they install the function to their own personal.xls file as well. If you want to pursue the personal.xls file route, and you don’t now have one, you can find out how to create one here…

http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx

• Jaime Hanson says:

Rick, I just found your UDF to concatenate cells and add a common character between each. THANK YOU for the timesaver!!

• Cyndy Kauhi says:

Oh my gosh Rick – your macro was a lifesaver for me! Merged 2300 names with a semi-colon delimiter in seconds! Thank you!

• Nilesh says:

Your macro UDF just works brilliantly. I was converting my excel data into chart prototyping software (mockuptiger) but it accepts transposed data not tabular and all data need to be comma separated. And the cells were 100 rows deep. I applied your formula
=concat(“,”,A1:A100) and boom it gave me what i needed. awesome.

• James says:

THANKS MUCH FOR THIS!

I was copying from a website with .aspx and it copied each word to a separate line in Word or Excel, even with paste as plain text. How ugly!

I was able to concatenate a range of rows in Excel, and pasted one page at a time into Excel, then copied result from one cell into Word, for easy word wrap & editing.

• Christina Boyle says:

yes, thank you so much for this!!

• K Porter says:

@ Rick Rothstein, your UDF is a dream come true. I’ve been struggling with CONCATENANTE, TRIM, and SUBSTITUTE for hours trying to get it to do what your function does quite elegantly. Much thanks for this.

• Kavita says:

Thanks Rick for sharing this solution.

4. Jason Morin says:

In the past I’ve used a UDF from Harlan Grove to stitch together multiple cells/ranges:

Function mcat(ParamArray s()) As String
‘This is free software. It’s use in derivative works is covered
‘under the terms of the Free Software Foundation’s GPL. See
‘http://www.gnu.org/copyleft/gpl.html
‘————————
‘string concatenation analog to SUM
Dim r As Range, x As Variant, y As Variant

For Each x In s
If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y
Else
mcat = mcat & x
End If
Next x
End Function

which allows expressions like

=mcat(“hi”,(A1:A4,A6:D6,C2),”foo”,A7:F9)

5. Rick Rothstein (MVP - Excel) says:

@Jason,

You might want to consider giving my ConCat UDF a try. My ConCat UDF provides the option to insert a common delimiter between the elements it concatenates together (think comma separated listing as one possible use) whereas Harlan’s UDF does not provide such an option (meaning you would have to specify each delimiter individually is there was to be a common delimiter between concatenated items)… and when the delimiter is omitted from my ConCat UDF (true, you do still need to provide the comma as an argument place keeper though) or the empty string (“”) is provided as the delimiter argument, then my ConCat UDF outputs the same text string as Harlan’s UDF. And, while this is not all that important in the overall scheme of things, my ConCat UDF does all of this using two less code lines than Harlan’s UDF.

6. Mark Nold says:

If you’re wondering why CONCATENATE() exists, since an ampersand is less ugly…

CONCATENATE() is less memory intensive and faster, but you’ll only notice in non-trivial spreadsheets.

I’d still use the ampersand simply for the reason that it’s easier to read and understand.

7. Clarity says:

I asked about this on Chandoo’s forum a while ago as I have always used “&” rather than CONCATENATE(). Apparently “&” is there from Lotus 123 compatability days:

http://chandoo.org/forums/topic/concatenate-or

Would be great if Microsoft could make CONCATENATE work over a range and with delimiters.

I guess CONCATENATE is a handy function, but not sure where it had got that name – too difficult to remember the spelling to be able to direclty type in the cell. Therefore, I always use ampersand, instead of going Insert >>> Function search for concat and get CONCATENATE.

Regards,

Hey Rick,
Thank you so much for posting the ConCat function. That is so useful, especially when I need to add multiple columns together or my data set fluctuates in size.

10. Dickson says:

I am trying to apply both functions to append:
“123? and “000001?
it turns out returning “1231? instead of “123000001?..
is there anyway i can get it to return “123000001? ?

11. Dickson, the cell with “000001? is formatted to show the leading zeros, but the value in the cell is really a “1?, so that’s what you see after combining the values.

To keep the formatting, you could use the TEXT function:
=B2&” “&TEXT(E2,”000000?)

12. Jeffrey says:

Hello, is there some function that allows the concatenation of a range of cells including the blanks as spaces. For example, I have a phrase divided into several cells so EACH letter occupies ONE cell, and spaces also occupy one space. All the functions I have tried dismiss blank cells, even the ones that have some sort of SKIPBLANKS argument, for example this one, http://www.vbaexpress.com/kb/getarticle.php?kb_id=817#instr. So something like BLUE HOUSE ends up concatenated as BLUEHOUSE. I’m using Excel 2010 so I don’t know if that has something to do.
Some help would be greatly appreciated.

13. Rick Rothstein (MVP - Excel) says:

@Jeffrey,

Assuming you mean the cells with the individual letters are in a contiguous range of cells across a row, give this one-liner (albeit a long one) UDF (user defined function) a try…

`Function JoinLetters(RowRange As Range) As StringJoinLetters = Replace(Replace(Join(WorksheetFunction.Index(Range(“A5:J5”). _Value, 1, 0), Chr(1)), Chr(1) & Chr(1), ” “), Chr(1), “”)End Function`

• joe says:

Rick:
Related topic:
When I merge two cell using Ampersand or Concatenate, EXCEL’s “FIND” function cannot find the merged text.
Example: A1 = john; A2 = smith; A3 = johnsmith. Then FIND cannot find johnsmith. Further, it cannot even find john or smith in johnsmith. All cells are formatted as Text.

14. Jeffrey says:

Hi Rick, thanks for replying so quickly. I tried to use the function but it didn’t work, maybe the A5:J5 part is affecting the calculations?

15. Jim Cone says:

Jeffrey,
Rick’s function worked for me.
I used the RowRange variable to replace the A5:J5 location in the function…

Function JoinLetters(RowRange As Range) As String
JoinLetters = Replace(Replace(Join(WorksheetFunction.Index _
(RowRange.Value, 1, 0), Chr(1)), Chr(1) & Chr(1), ” “), Chr(1), “”)
End Function

16. Jeffrey says:

Excellent, thanks to the both of you. This function will help me a lot.

17. Jeffrey says:

One more thing, if its not too much trouble. Is it possible to modify it so that instead of a range it asks for a starting point and a length?

18. Jeffrey says:

I could not find an edit button so sorry for the triple post, feel free to erase my previous posts. You see I have the X and Y positions in one sheet, the length in another and the actual info in another. The function itself will be used in a fourth sheet so that’s why I added the last comment. So starting position is comprised of 2 coordinates, each one inside a single cell. Again, if this is too much work just ignore this post.

19. Rick Rothstein (MVP - Excel) says:

> I used the RowRange variable to replace the A5:J5 location in the function…

@Jim Cone

Hmm! Bad editing by me on going from a macro, originally, to a UDF instead. Thanks for picking up on that Jim… much appreciated.

20. Rick Rothstein (MVP - Excel) says:

> One more thing, if its not too much trouble. Is it possible to modify
> it so that instead of a range it asks for a starting point and a length?

@Jeffrey,

This would change the code from a UDF to a macro… no trouble doing that, but you need to clarify a couple of things for me first. Instead of “starting point and length”, what if I set the macro up to process the selected range of cells? That way you could select all the cells in the row from the first to the last letter and then run the macro? I could also make the selection process as part of the macro if that would be better for you… that is, you would run the macro and the first thing it would do is select the range across the row containing your letters. Let me know if either of these ideas appeal to you. The other question I have is… now that it is not a UDF, it doesn’t know where to put its output. How did you want to handle that? I could have the macro ask you to select the output cell… would that be okay? Or do you have a reserved cell in mind where you always want the output to go to? If so, which cell?

21. Jeffrey says:

Sure, it can be a macro, that’s not a problem. The thing with the range is that it works for some cases but not for others. Let me give you some background information. I managed to transform a data spool (.txt) to a matrix in excel using the “Import from Text” functionality, so that each character in the spool occupies a cell in the matrix (This is Sheet 2). Sheet 1 acts as a reference containing all the information needed to validate the info in Sheet 2. So based on the Reference Sheet I use your formula in Sheet 3 to validate that the “sections” of the Spool Sheet are where they are supposed to be and have the correct length and data type. The Reference Sheet contains cells with X position, Y position, length, info type (String, Number and DateTime), and others, for each of the sections that need validation.
Considering this, it would be nice if the formula or macro considers X, Y and length in Reference Sheet, goes to the Spool Sheet to the specified coordinates and joins the amounts of cells stated in length. As you can see its pretty convoluted because after this is done, I have to convert the resulting string to the data type defined in the Reference Sheet for that “section” or “line” so that I can validate further. Hope I clarified the situation properly, if not I will gladly give more details about it.

22. Rick Rothstein (MVP - Excel) says:

@Jeffrey,

What is X and Y… Row and Column, Column and Row, something else?

23. Jeffrey says:

X represents the Row and Y the Column.

24. Rick Rothstein (MVP - Excel) says:

@Jeffrey,

I got a little confused reading that.;-) What I decided to do is leave it as a function and have you enter the sheet name, X value, Y value and Length (in that order). So, you would use a formula like this on your worksheet…

=JoinLetters(“Sheet1?,2,5,25)

where that would mean the letters are on the worksheet named “Sheet1? starting in cell E2 (X being Row 2 and Y being 4 for Column “E”) and running across for 25 cells. Here is the code to handle this input…

`Function JoinLetters(WS As String, X As Long, Y As Long, Length As Long) As StringJoinLetters = Replace(Replace(Join(WorksheetFunction.Index _(Worksheets(WS).Cells(X, Y).Resize(, Length).Value, 1, 0), _Chr(1)), Chr(1) & Chr(1), ” “), Chr(1), “”)End Function`

25. Rick Rothstein (MVP - Excel) says:

> …and Y being 4 for…
@Jeffrey (again),

Sigh! That was supposed to by “…and Y being 5 for…”

Sorry for any confusion my typo may have caused.

26. Jeffrey says:

It’s working very well Rick but I’m encountering a side effect when I try to remove the following empty spaces. Example: The referenced length is 80 and the line itself contains 19 characters (16 letters and 3 spaces) after using your formula I use the LEN() function and it states that it has a length of 49. The JoinLetters string shows up something like this ‘Xxxxx xx Xxxxx Xxxx ‘. Basically 19 characters and 30 spaces. Its weird because its not showing a length of 80… ideally it’s LEN() should be 19 but that would involve including a space remover inside the JoinLetters function that mistakenly could take out the inner 3 spaces which are needed and not just the outer 30 spaces.

27. Rick Rothstein (MVP - Excel) says:

@Jeffrey,

Does this version work the way you want?

`Function JoinLetters(WS As String, X As Long, Y As Long, Length As Long) As StringJoinLetters = Trim(Replace(Replace(Join(WorksheetFunction.Index( _Worksheets(WS).Cells(X, Y).Resize(, Length).Value, 1, 0), _Chr(1)), Chr(1) & Chr(1), ” “), Chr(1), “”))End Function`

28. Jeffrey says:

Works like a charm Rick, thank you very very much for your time and expertise. You have given me a significant head start in this endeavor. I’m off to battle with the data types validations.

29. Contextures Blog » Excel Formulas Show in Cell says:

[…] year, I showed that you could combine text in Excel by using the ampersand (&) operator, instead of the CONCATENATE function. That makes it much […]

30. aseem says:

How to combine common cells into one cell.
eg a1 to a7 have the same content like docno1 but i want to show docno1 at a1 only remaining i want to show blank spaces .how can i do this.

31. ozibear says:

How to I make a cell reference (or part of the reference) be the value of a cell

eg let cell F8= 123 and if C4 has a value of “F8” , and I want E4 to be equal to the value of (the cell refered to in C4) , ie E4 looks only at C4 to then be refered to F8 to get its value of 123

I want E4 to be something like =(C4) where something other than these parentheses is required

32. Rick Rothstein (MVP - Excel) says:

@ozibear,

Try putting this formula in E4…

=INDIRECT(C4)

33. Tom says:

I was searching for a little help then I found this page, and I received a lot of help. Thank you for taking the time to post.

34. TXtom says:

Some issues and solutions with concatenating things:
= >256 – Data longer than 256 characters might not transfer fully in certain actions, like edit/copy/worksheet. When you use & for text and long sentences or paragraphs, this can be a problem. If you can identify cells with >256 chars, these can usually be manually copy-and-pasted to the destination. Better than nothing.
=TRIM – an excellent function when combining words, especially when some are ends of sentences or paragraphs or you-just-don’t-know and may contain internal spaces. The A1&” “&B1… can give you double spaces. Surrounding this all with =TRIM(yourlistwith&here) will limit all spaces to just singles. Too bad there isn’t something like an option for single commas or hyphens, but that can be done with clever SUBSTITUTEs or maybe find-and-replace. And if you’re an old typist who believes there should be double spaces after the period of a sentence, doing a replace of . with . will work (if you just do it once!).
=IF – Sometimes the use of IF statements can be helpful in tweaking cell contents though it can become tedious. You might want to skip cells with the number zero in them, or otherwise conditionally include a cell, so just put an if(A4 30 explicit references, so the & approach can avoid this where a CONCATENATE or SUM function won’t let you get higher than A30 when you give it a list of A1,A2,A3… (yes, I know A1:A30 is the same but this is just for illustration – you’d run into the problem if there were more than 30 terms like B4,C5,A2,F18…)
= Click – for long or complicated arrangements, use your mouse and click on the specific cells in the desired order for CONCATENATE, by typing =CONCATENATE(,,) and so on and let the computer put in the right cell reference. If you then want to get fancy you can replace all the commas with &” “& and get rid of the CONCATENATE function.
=ADDRESS() – can be handy to build up a reference (A1) from certain calculations, especially in conjunction with ROW() and COLUMN() functions [do you know that just plain ROW() will tell you the row number of the cell in which ROW() is placed? Ditto for COLUMN(). Can be very handy.]
=INDEX()
=INFO()
=IF()
=INDIRECT() some of these have been mentioned previously
=OFFSET()
… and other functions can be useful in sophisticated gatherings of data.

Excel can be very powerful. I tell people I could build a house with Excel. It’s not just for Math.

35. When I joined (Con) two cells the value in one of which was in Superscript font with & the Superscript
disappears and normal font is shown. How to correct this. I am doing this for Algebra so the student see
X raised to 3 etc

36. In cell A10 I write ‘X’ In Cell A11 I write ‘3’. I format cell A11 and select Superscript.In Cell A12 I write ‘=A10&A11’ The result is ‘X3’ But ‘3’ is not in Superscript.

• C.Farrell says:

try &char(0179)

37. Douglas Milby says:

If I use a range were a number of cells will be blank and the quantity varies from use to use,
how can I keep from getting this:

Rob B, Michael B, Ben E, Bret O, Chris R, , , , , , , ,

38. Doug Glancy says:

Lo, these many years later I remembered Rick’s UDF at the beginning of this post and have added it to my personal addin. Don’t know why I waited so long. Thanks Rick!

39. TheGuy says:

Any way to concatenate a cell with itself without creating a loop ? (Only update when the other cell changes and add it to it current text)
Here is what I would like to do…

Example:
– B2 dropdown list contains the item list dog, cat, cow, chicken
– I select “cow” from the drop down
– B3 gets populated with “cow”
– I select dog from the drop down
– B3 gets populated with “cow, dog”
– I select “cow” again
– B3 does not get updated as the string already contains “cow”
– I select “chicken” from the drop down
– B3 gets populated with “cow, dog, chicken”

Effectively I’m trying to create a check box list using a drop down :P
So far I’m stuck with reference loops…

40. to says:

When I initially commented I clicked the “Notify me when new comments are added”
checkbox and now each time a comment is added I get four e-mails with the same comment.
Is there any way you can remove me from that service? Many thanks!

• Thank you for letting me know that you’re getting several emails when comments are added to the post.
If you still have a copy of one of those emails, there should be a link that lets you unsubscribe from the comments.
If that doesn’t work, please send me an email, and let me know what email address you used for the subscription, and I can delete it for you.

41. Tina Harrison says:

Does anyone know how to sort letters alphabetically within a single cell? I have concatenated a series of 4 or 5 grades (A – E) into one cell, delimited by a space in between each grade, and have managed to sort by pasting the result into another call as a value, then separating back out to separate cells before sorting by row. A bit long winded, does anyone have a neater solution ?

42. Tanzil Sayyed says:

How to use concatenate if I want to write 2nd Sentence below 1st sentence in a same cell.

• @Tanzil, you can use CHAR(10) to create a line break within the formula. For example:

=A2 & CHAR(10) & B2

43. Georgieva says:

Hi, I am trying to merge two cells and format them but it doesn’t work the way I wish it would. I need for example cell A1 0.880567 and B1 0.0762768 to merge in the format 0.88 ±0,08. I would really appreciate it If someone could help me. I manage to merge them but I can’t formate the cell to have only two decimal points. So far I have =CONCATENATE(A1;C1;B1)with C1 being ±

44. Robert Tronrud says:

Hello:

Using Excel how do I merge these two cells?? They are both in the same column and the BFS field is a continuation of the file name in the cell above it.

/tsmnoddpstg4/000D91A9.-
BFS

The correct name should be: /tsmnoddpstg4/000D91A9.BFS

Thanks

45. Usama Jafri says:

Can Any Body Tell Me HOw Can I Copy Inner Text From A Div Tag Into Excel File Using VBA..??

46. Helen says:

This worked like a charm, Thanks!!

This has been a fascinating string. I am very aware of the & and it’s powers with concatenation. Here is the scenario that I’ve been presented with and I have tried everything I know and I am still at a loss.

A person has a string in one cell with name and address. i.e.

A1 B1 C1
Steve Nadel 1 Main Street, Bronx NY 10461 718-555-1212

They would like it so that when they click in B1, they can run a macro so Excel places the City, State and Zip in the same cell, but on two lines having the City, State and Zip directly under the address (starting with Bronx in this case.)

I’ve tried everything, by separating all the information with concatenating into two separate cells. Since there is already a coma between the street and City bringing it back with the “Text to Column” feature is the easy part. The tough part is writing a macro that will do that for every line which all are unique.

The macro runs well, up until the Shift+ Enter, which is great manually, but doesn’t seem to work in macros.

Steve

48. ofir says:

can i combain cells inside Offset function?

lets say i have Tab Named: A

A and i want Value from Cell B31.

i want to go to sheet2, enter into Cell B3 –> A

insert into Cell B4–> B31

and use an offset using cells B3 and B4 which will be directed into Sheet A and recieve value from cell B31

49. Norm says:

HI, all good but is it possible to have a cell with say 100 in it as a number with a descriptive say m2 (metres squared or foot square for instance) with no formula in the cell needed as the 100 is manually entered but for the 100 in this cell to be used as part of the formula where it is being used as a multiplyer. I dont need the m2 to come accross. Others have asked this sort of question but I havent seen an answer by anyone. Looking for some help, thank you al in advance,
Norman

1. October 14, 2014

[…] don’t have many worksheet UDFs in my tools addin, but one exception is Rick Rothstein’s CONCAT function, which I found on Debra’s blog. It’s great because, unlike Excel’s Concatenate […]