|
How To Add A Number With Text In The Same Cell
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
How To Add A Number With Text In The Same Cell - Excel
|
View Answers
|
|
|
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
Thanks
Similar Excel Video Tutorials
Text Cell Range In A Function
- See how to use a cell range typed into a cell in a function. If I was counting the number of times the word "Rad" was in the range B13:B20, ...
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
Similar Topics
Hi,
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,
hilss
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?
THANKS!!!
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
Hi
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.
Sincerely
artner
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.
Hi,
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.
Hi,
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?
Thanks
Hi
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
Hello,
I need a formula that would return a certain text when a certain number is entered.
Example:
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".
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.
Hi,
I am new to this forum so please let me know if I am doing something wrong.
I have the same question as http://www.excelforum.com/excel-gene...o-in-cell.html
BUT:
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:
22jim
5
7
5mary
Needs to equal 39.
Thanks for all your help,
Thomas
Hi,
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.
Cheers.
Hey,
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:
=IF(F12>0,F12*85,L12)
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.
Hi,
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.
Cheers,
Bogia
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?
Thanks!
Hello.
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!
Nicki
Got a bit of a strange one.
Looping through a group of cells, and if the cell value is blank, I want to put a value in the cell (in this case, 0).
Heres the problem. When the cells are visible, it works fine. When the cells are hidden, the activecell.text is equal to "". If I run the code on the same cells when not hidden, the text is equal to the cell value (normally a number).
Code:
Sub RemoveBlanks()
Range("A1").Select
While ActiveCell.Row
Hi,
I am raising this question for someone who has no internet (yes there are still some )
Is it possible to take a value from one cell (lets say a value of 20) and insert that cell's value into another cell already containing some text?
As an example, lets say a cell was set for "number of iterations" (i.e the 20 mentioned previously) and another cell reported the result of some calculation based on this number, with a cell next to that that had some text saying "this is the result of 20 iterations" - where the 20 is obtained from the other cell?
As you can probably guess, the number of iterations can be changed by a user of the worksheet, and therefore the text/label cell needs to reflect this possible change as well
Thanks in advance for any assistance :D
Tmm
Hello all,
Trying to write a macro that will add standard gridlines to a worksheet given these variables:
Starting in row 2, column 2, gridlines through the last cell with text in row 2
Gridlines through the last row with text of the worksheet.
Text could go to any column letter and any row number.
Like this:
row1
column1
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
text
Help is most appreciated!
|
|
