Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell
May 2, 2014
I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).
number_forma.png
I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.
In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";
custom_format.png
As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.
Here is a screenshot of what the original and custom formatted cells look like:
custom_formatted_cells.png
As you can see the custom formatting works and does exactly what I wan
The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).
To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:
CONCATENATE("SOME STRING ", B1)
Where B1 is the custom formatted cell.
The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:
concatenate_formula.png
As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".
I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)
My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?
*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
I am having a terrible time with Excel today. The version I'm using is Excel 2010.
I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.
The simple division is =13588/12
The output in accounting format should be 1,132.33.
Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.
I have a set of data that has a company name using commas to separate INC and LLC from the company name. e.g. Acme Explosives, LLC
I'm using Excel 2010, and when I try to use the find/replace functionality to find commas, I get an error message saying "We couldn't find what you were looking for. Click Options for more ways to search.
Short of editing all the fields manually (only about 300, so not too bad), I'm hoping there is a simple way to replace these commas with nothing.
I have two columns with entries consisting of numbers. I'm trying to match between the columns but some names dont match because of the formatting.
Example
065 matches 065 (I have that green triangle on the cell that indicated "The number in this cell is formatted as text")
However 120 doesn't match with 120 (only one of them have that green triangle)
I tried to format all the cells as text at it seems the match only works when the green triangle is available. The green triangle seems to only show its self when I double click on the cell. I have thousands of entries so that doesn't work. I also tried to use the "Text to Columns" but it coverts entries like 002 into 2 which I dont want.
In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).
How to modify a custom cell format to make data entry easier.
My goal is to have the output look like this: 'A12B-C20D: SET 10: Text string here'
Inputs needed are:
1-3 digit number between A & B 1-3 digit number between C & D 1-2 digit number after SET Text string of variable length
Where I am stuck. So far I can get "A12B-C20D: SET 10:" to show up properly when 122010 is input. The custom format I am using looks like this: "A"##"B-C"##"D: SET "##": "
When I try to incorporate the "@" symbol the first part of the formatting goes away and the output shows up as "122010 Text string here".
I have tried: "A"###"B-C"###"D: SET "##":";;-;_@ "A"###"B-C"###"D: SET "##": ";;@ "A"###"B-C"###"D: SET "##":";_@ "A"###"B-C"###"D: SET "##": ";;;@
I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.
Here's a few examples of what I mean:
Original text RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12 MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38 RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01
would like to split into...
Cell 1 RED008 - Wickaman and Hoodlum bandwidth MFR005EP - Various Artists bandwidth RAZORS010 Future Cut bandwidth
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest() Dim n Dim V Range("A1").Select V = ActiveCell.Value
I have a list of names (text string) in a column (A) that have associated whole numbers in another column (B). What I would like to do is create a formula that multiplies the text string in cell A2 (for ex.) by the whole number in B2 and return a "list" of the text string in A2 equal to the whole number by which it is multiplied.
Here's a visual example of the results I want to achieve:
Using the above example, I would like to end up with a list in column D with 159 total rows with the text contents corresponding to the whole number in column B.
I am trying to concatenate entries into one cell so that when uploaded, the comma-separated contents will be treated as tags. copperberry sample file.xlsxSee attached sample file. Wherever there is a 1 in a row, I want to take the column header text above that 1 and concatenate it with subsequent text in the cell at the end of the row. See sample end result in cell J2. I assume I need an IF statement, but I'm not sure how to phrase it to collect all the concatenations needed. There are 200+ rows.
I'm working on a sheet for our accounting section which has been in use for quite some time, which means, that I'm not supposed to change a hell of a lot in it since it's accounting and everybody is afraid of changes.
Coming to the point, I have the following problem:
(1) I have a date. (2) I need that date in this format: "mm-yyyy", written in a certain cell. (3) BUT: the cell should NOT contain the date itself, but JUST the text (some tables in the background need this format) (4) Changing the cell to text-format is not eligible, since it puts an " ' " in front of the numbers. (5) The cell should be formatted as a number.
So, in conclusion:
I need a string/number with the date in this format: "mm-yyyy" in the cell formatted .NumberFormat = "0".
I'm working on an existing workbook...
I decided to add some code and a screenshot. This code will return the number, which lies behind the date, and write it into the cell. So that's not what I want, but maybe the code clarifying what I need anyway.
The screenshot shows the wanted output : screenie.png
In Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
I need to generate conditional formatting in Excel 2010.
For the planned date of material submittable should turn yellow before 10 days ago & planned date of material submittable should turn Red when overdue.
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
I'm trying to get Excel 2010 to automatically generate a custom timeline in a separate tab using data I enter in a different tab. In the attached example, the data in the first tab ("daily log") tracks the number of trucks parked at a certain yard by the state they are registered in, their company, and their ID number. I would like Excel to automatically generate the timeline I've created by hand in the second tab ("Timeline (desired outcome)"). I need a formula that returns a "1" if several conditions are met (see comments in example) and a " " if those conditions are not met. I played around with IF(AND...) functions, but I've never used them before and couldn't make them work. There are a few complicating factors: there aren't a consistent number of trucks at the yard per day, and some trucks changed their ID numbers during the data period. I use a blank row, highlighted in dark grey, to visually differentiate days. This row can be filtered out with the "null" column.
Column A has a custom format of 000. Therefore the numbers in that column always have leading zeroes if they're less than 3 digits long.
I want to copy these leading zeroes to another column, but I DON'T want to have to custom format the new column. I just want the values copied over with any leading zeroes, if applicable.
When I "Paste Special" and select "Values", numbers like "1" that show up as "001" in Column A will show up as "1" again in the new column. How can I get the actual value "001" to be all that's in the new column?
If it's easier for you, pretend I need to copy over these custom formatted values in to another program which does not retain any custom formatting.
I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.
Long story short, I will be saving several variations of this workbook every so often when I get new data.
The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.
Is it possible to assign relative macros to custom ribbons?
From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?
However, whenever I run the code, if there's even on word that is bold in the cell, then the entire text string in the cell turns bold. How can I stop this from happening? i want to keep the format of the text string the same, only remove and replace the items listed in the code.
I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
Assumptions: -Will ignore blanks -Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
Scenarios: c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4 etc
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
We are in the process of switching from running Excel 2007 under Windows XP to running Excel 2010 under Windows 7. Most of my Excel reports are not having any issues. However, I have several macros that cause Excel to crash when they go to save the file. Through trial & error, I have found that the problem seems to occur after deleting custom lists that were created earlier in the macro. Here's the pertinent code:
' Declare Variables Dim VarListNum1 Dim VarListNum2 Dim VarListNum3
[Code]....
When I execute the macro line-by-line, it is creating & deleting the custom lists as expected. However, if I try to save the file after any of the custom lists have been created, Excel crashes. Has something changed in Excel 2010?
i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.
I can not seperate this into 2 fields as it would break the program this database belongs to.
this database is linked from excell to access i am using excell to update an access database
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
Excel 2010.I'm using this code to print user's accessing a file:
[Code] .....
However as someone could just delete the printed user log in the Sheet2, and also you can't use this if you protect the sheet, which defeats the purpose.
I want it to print the log into a separate file. Can I add in the file path/name to this code somewhere?
