Email:      Pass:    Pass?

Free Excel Forum

How To Add A Number With Text In The Same Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How can I add a number with text in the same cell excel? Example:

A1 = 100 KB
B2 = 200 KB

C3 = Want to equal 300 Kilo


Similar Excel Video Tutorials

Helpful Excel Macros

Format Cells as Text in Excel
- This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Filter Data to Display the Results that Begin With Specified Text or Words in Excel - AutoFilter
- This Excel macro automatically filters a set of data based on the words or text that are contained in the beginning of t

Similar Topics

I have to maintain freight cost according to the shipment weight. Our shipper has different charges for different scale/quantity of shipment. For example, according to the attached picture; Rate for up to 10 Kilo is $ 20 per kilo. For weight more than 10 kilo and up to 20 kilo, Rate will be $ 15 per kilo and so on.

Now, suppose my shipment weight is 28 kilo. My cost will be $ 200 for 10 kg, $ 150 for next 10 kg and $ 80 for the last 8 kg. In total $ 430.

I have to calculate the total cost separately. I wonder if I could devise a formula by which I could put the weight in a single cell and get the freight cost instantly.

Thanks everybody.


I would like to use the text that's available in a certain cell to create a formula in another cell. Below is a simplified example:

In cell A1, I have the text "= A2 + A3".
In cell A2, I have a numebr equal to 2
In cell A3, I have a number equal to 5
In cell A4, I have a number eqaul to 3

In cell A5, I would like to use the text in A1 ("= A2 + A3") to create the formula: = A2 + A3 + A4, such that the result of A5 is 10.

Can someone help?

Many thanks,

I am working on a macro that loops through a bunch of cells in an Excel 2007 worksheet. It would be most convenient if I could determine for each cell what is in the cell, where the choices are 1) a number formatted as text, 2) a number, 3) a blank, 4) text. The flow in the loop goes differently depending on the answer. Identifying numbers and text is easy, but I'm having trouble figuring out how to tell whether the text is actually a formatted number. Can I set up some error handling, then try to convert the text to a number? Is there a better way?

This one should be easy for all of you excel wizards...but I am having trouble figuring it out. I need to create a cell that has text in it but actually has the value of a number, thus when mulitplied by another cell returns a value.

EXAMPLE: cell A1 has a value of 100 and cell A2 has text "No discount" entered but is equal to 1. Thus when cell "A1" is multplied by cell
"A2". The returned value should equal 100.

"a1" * "no discount" = "a1"

can anyone help?


Hello Excel Team Members,

I am looking for a solution to my problem. I am trying to Group my data into ranges using vlookup formula. Below is an example of what I am trying to do. As you can see that if my GM% is -1% it is giving me #NA. It should display "Less than 0.00%". Similarly, if my GM% is 100% than it should display "Equal to 100%". The rest of the formula is working. Here is my Vlookup formula =VLOOKUP(O4,$Q$2:$R$16,2)

GM% (CPD) Vlookup Formula Table Range Range formula -1% #N/A 0.0% Less than 0.0% "Less" & " than " & TEXT(R2,"0.0%") 0% Equal to 0.0% 0.0% Equal to 0.0% "Equal" & " to " & TEXT(R3,"0.0%") 5% 0.0% - 10.0% 0.0% 0.0% - 10.0% TEXT(+R4,"0.0%") &" - "& TEXT(R5,"0.0%") 15% 10.0% - 20.0% 10.0% 10.0% - 20.0% TEXT(+R5,"0.0%") &" - "& TEXT(R6,"0.0%") 25% 20.0% - 30.0% 20.0% 20.0% - 30.0% TEXT(+R6,"0.0%") &" - "& TEXT(R7,"0.0%") 35% 30.0% - 40.0% 30.0% 30.0% - 40.0% TEXT(+R7,"0.0%") &" - "& TEXT(R8,"0.0%") 45% 40.0% - 50.0% 40.0% 40.0% - 50.0% TEXT(+R8,"0.0%") &" - "& TEXT(R9,"0.0%") 55% 50.0% - 60.0% 50.0% 50.0% - 60.0% TEXT(+R9,"0.0%") &" - "& TEXT(R10,"0.0%") 65% 60.0% - 70.0% 60.0% 60.0% - 70.0% TEXT(+R10,"0.0%") &" - "& TEXT(R11,"0.0%") 75% 70.0% - 80.0% 70.0% 70.0% - 80.0% TEXT(+R11,"0.0%") &" - "& TEXT(R12,"0.0%") 85% 80.0% - 90.0% 80.0% 80.0% - 90.0% TEXT(+R12,"0.0%") &" - "& TEXT(R13,"0.0%") 95% 90.0% - 100.0% 90.0% 90.0% - 100.0% TEXT(+R13,"0.0%") &" - "& TEXT(R15,"0.0%") 100% 100.0% & Above 100.0% Equal to 100.0% "Equal" & " to " & TEXT(R14,"0.0%") 105% 100.0% & Above 100.0% 100.0% & Above TEXT(+R15,"0.0%") &" & "& TEXT(R16,"Above") 100% 100.0% & Above Above

If someone has a better idea, please share with me. I would appreciate it. I got the idea of setting up ranges this way from Mr Excel podcast 912/913. Thanks.

Hey there I am having an issue where I cant find a formula to do the following:

I have four columns two of which contain identical text (which is sorted in a different order). Next to each text cell is a corresponding number in the next column. So A1 contains text and B2 contains a corresponding number. Column C2 contains text and D2 contains a different corresponding number.

What I want to do is to find the matching text from the two text columns and merge (the identical text)and move the corresponding number from the duplicate cell to the same row so I end up with:

A1 (text) B2 (number) D2 (number) or text number number

Another way to explain it is:

I have two lists of matching text but with different numbers in the cells next to each column of text. I want to move the matching text and the number next to it.

Would be very grateful for some help cheers


Like it says in the topic title , I want to erase all line breaks which I am not using from my work sheet. But still keeping the line breaks which has something written to it.

Since my English isn't good please think this as a example of one cell.

- Example text number 1
- Example text number 2
- Example text number 3
- Example text number 4
- Example text number 5

Using "Find & Replace" I got to replace the ones I want to erase with nothing, so its blank. Too bad the Excel doesn't replace the line breaks with it, so this is what I get.

- Example text number 1
- Example text number 2

- Example text number 4


The result which I want to have is:

- Example text number 1
- Example text number 2
- Example text number 4

I know about the Clean Function but that function eases all the line breaks from the cell, which I dont want to.

So my question is can the excel do this?
If not that whould mean that I have to delete the line breaks by hand for hundreds of cells.


In a normal excel sheet I have a summation formula adding up data in a particular column. What is the function to have another cell equal to the number in the summed cell. For example:

Cell B1 = 1
B2 = 2
B3 = 3
B4 = Sum of B1:B3 and therefore = 6

Now if I want cell F10 to equal just the value which is "6" without using the =B4 how can I do that.

Is there a formua that will just allow the text of the cell and not the formula tied to it equal to another cell.

Basically I just want to strip the formula in cell B4 (without removing it of course) and have my F10 cell equal to the end result of the formula without using =B4 (which will reference cell B4 thus using the formula as well)

Thank you in advance.


I have a cell that has this number format: "VERY VERY VERY VERY LARGE TEXT" 0,0000000000%.
I need the text to be there to understand what the cell means, and I need it to be a number because other cells use this cell's value to perform calculations.

If I let the column fit to the full size with the number in it, it displays: VERY VERY VERY VERY LARGE TEXT 3,5000000000% If I put the the whole text (TEXT, not number) in a cell with a smaller column width and select for the cell alignment: Text Control, Wrap text to be checked, it displays: VERY VERY VERY VERY LARGE
TEXT 3,5000000000% However if I select wrap text in the cell that has the number and the number format, wrap text does not work (what might make perfect sense cause now it's actually a number), and it displays: #################### Is there any way to get it displayed as I want, and also having there a number and not a text?
TIA & Regards ...

I have some data which I download from an external source, which is "space delimited". I need to get it into columns properly.

The data comes in like this:

Example of Row 1: Text Number Number Number
Example of Row 2: Text Text Number Number Number
Example of Row 3: Text Text Text Number Number Number

All of the text in a given row needs to be in one cell.

this is the problem - if the "text" was just one word, then Text to Columns would solve my problem. But, some of the "text" entries are one word, some are 2 words, some 3, and the Text to Columns function separates everything with a space in between, so some of the descriptions end up spread across 3 columns, and then the numeric data is pushed out and doesn't line up with the other numeric data.

Any solutions would be acceptable, but a formula would be fine.

Sorry for the rambling post. As always, thanks in advance.


I'm using Excel 2007 and I need to extract values from rows from a data dump into columns. Here is sample data (this is dummy data, but the format is the same):

> Log for Account 55555 Institution Number 55
> ----------- text text text text text text
> Certificate 5555555555 line number 555 via USD 555,555.00-
> text text text: date number percent 0 percent 0
> number text text text text text text
> text text text text text date; text text text date
> Text text text text percent text text
text text text number text. Text text text text text.
Text text text text text text percent
Text text text USD 5,555.00 text text text text text text
> Text text text text text text text
> ----------- text text text text text text
> Certificate 5555555562 line number 555 via USD 555,562.00-
> text text text: date number percent 0 percent 0
> number text text text text text text
> text text text text text date; text text text date
> Text text text text percent text text
text text text number text. Text text text text text.
Text text text text text text percent
Text text text USD 5,555.62 text text text text text text
> Text text text text text text text
> Log for Account 44444 Institution Number 44


I want to extract the number values (in bold) into a sheet in columns that will look like this:

Account Certificate Amount Int. Amount
55555 5555555555 555,555.00 5,555.62
55555 5555555562 555,562.00 5,555.62
44444 ....

The data dump continues and and each Account can have any number of Certificates in it. Each line of dummy data represents a new row. What's the best way to go about extracting this data?


I need a formula that would return a certain text when a certain number is entered.

A1: Dog B1: 9
A2: Cat B2: 4
A3: Rabbit B3: 7
A4: Bird B4: 2

So, if 9 is the answer I want to get the value in the adjacent cell "Dog".


Can I have some formula help please.

If the value cell K5 is greater than or equal to the number 10 then let Excel type 10 in cell L5

E.g number in K5 is 50 then the number in L5 will be 10
E.g number in K5 is 10 then the number in L5 will be 10

If the value cell K5 is less than the number 10 but greater than the number -5 then let Excel type what ever number is in K5 into cell L5

E.g number in K5 is 8 then the number in L5 will be 8
E.g number in K5 is 4 then the number in L5 will be 4

If the value in cell K5 is less than or equal to the number -5 then let Excel type -5 into cell L5

E.g number in K5 is -24 then the number in L5 will be -5
E.g number in K5 is -5 then the number in L5 will be -5

Oh by the way I don't need all the fancy colours shown above ..... I've just done that to make the reading of this post easier.

Many thanks

I have cell, which has 3 conditional formats on it:

1) if cell value (A1) is equal to cell (A5) then I want the text to be black and bold in cell (A1)
2) if cell value (A1) is greater than cell (A5) then I want the text in cell (A1) to be red and have a strike through
3) if cell value (A1) is less than cell (A5) then I want the text in cell (A1) to be red and have a strike through.

The problem is that cell A5 has a formula in it (simply =A7) and the first conditional format does not work and I always get the number red and with a strike through even when condition one is met.

I have tried a number of different things including changing the conditional format etc.

I think the best way would be to mange the cell through VBA to read the following:

If Cell A1 = Cell A5 Then
Change text colour to Bold and black
Else if Cell A1 > A5 then
Change text colour to red and strikethrough
Else if cell A1< A5 then
Change text colour to red and strikethrough
End If

I know this is not the VBA and I need help on what exactly it would be to get this to work.

Thank you

A B C D HS Codes Description 1234567 Text, text, text 7654321 Text, text, text Please I need a formula to do the following: (Data validation) Drop down list on cell D When selecting I need the numbers to go to the C cell and text to the D cell. Thank you.


I am new to this forum so please let me know if I am doing something wrong.

I have the same question as


Instead of summing with text such as Jim 22 (ie with a space and the number after), I need the number before the word and no space:


Needs to equal 39.

Thanks for all your help,

I need to put a number as a text. It is 14 digits long (35071245130000). If I change it from a number to a text, it re-formats to a scientific number (3.50712E+6).

I can not use the ' in front of the number because it makes all of my vlookups mess up.

The reason I need this changed is because I have a several databases/excel spreadsheets that are being linked through Access. The number is stored as text in the other databases. Because of this, the spreadsheet with the number is not recognizing the number version and the text version as the same.

Does anyone have any suggestions on how to make excel show this full number when formatted as text?

P.s. I have Excel 2010. I do not remember having this problem with other versions at my previous employer.


I want to set up a formula where a given number in cell A1 between 1-20 will come up with text A if the given number is 1-9, text B if it is between 10 and 14 and text C if it is above 15 (ie. 15-20). I would want this text to appear in cell A2 as the original given number would still need to be visible.

I hope I've explained that well as I haven't put it into context of my spreadsheet but that would just complicate things further.

Any help would be appreciated.



I am trying to make an If statement that will look determine if a cell is a number or text, if it is a number than display that number times a constant, if text than display a different cell. Here's what I've got so far:


The true part is working, but the false returns a #value! error. I'm thinking I need to nest another if statement but don't know how to tell excel to differentiate between numbers and text. Note: The text in the cells are all different, so I think I'm looking for something that indicates generic text.


I received an excel sheet with about three hundred records. All the information is stored in one cell with three lines of text in each row. The first row contains the First & Last Name, Second Row is Telephone number, and third is Fax Number. When I used the Delimited Function it does not identify the second and third row of the cell, but just the first. When I use the fixed option it is impossible to include all the records without cutting off a name or part of a telephone number in the process.

Is there a better way to separate text in cell (with three lines of text in each cell) into multiple fields? Any help would be greatly appreciated.

Hi all, I have a number of cells which have the number as text error, which can be corrected by either individually correcting each cell, or setting the cell format to number. Is their any way in VBA to to convert the from text to number? i've tried the following;

Selection.NumberFormat = "0" , but this doesnt seem to resolve the error in the cell.

Any help appreciated.

Hi all,

Could anyone help me to separate the text only from the below text and number together.

eg: cell A1 = three123 become three by itself in one cell without 123
I don't want to use LEFT(A1,5) or RIGHT(A1,3).

The text is vary when I updating the data. eg: it could be two2345 at cell A1 and I need to have "two" at cell B1 and "2345" at cell C1.

Very much appreciated.



I am trying to fix a spreadsheet for a co-worker....

He has a cell of mixed formulae and text that does not fully display. The cell is more that large enough (vertically and horizontally) and the wrap text setting has been selected.

Basically it contains text, formula, text, formula & (ref to cell of text) & (ref to cell of text).

The problem is that the last part of the last reference to cell of text always cuts off. I've tried changing the cell size, etc, etc, with no success.

Any ideas? Is there a limit on the number of characters that can be displayed?


Hello, html5468 here! I am new to Visual Basic and was wondering if it were possible to check if a cell's value were equal to a certain string and if I could then change the color of the row the cell is in. What I am attempting to achieve is, if the cell in column B were to contain the word "Black" it changes the text color to black. If it were "Blue," it changes the text color to blue. I am not having trouble at checking the cell's value or changing the text's color, rather I don't know how to grab the cell's row number. If you could help me out, that'd be great. Thank you in advance!

I have searched and searched and can not find a formulas that I can use to extract a number from a cell.
I am trying to pull the 13 digit number that is within a cell. The cell sometimes contains TEXT, 13 digit number, and a date. Other times the cell can contain TEXT, 13 digit number and a phone number.

Does anyone know of a simple format to just place the 13 digit number into a new cell?

Thank you for any and all help!