Concatenate Text From Left & Right Of Cell With Another
Mar 10, 2008
I'm working with a datafeed and basically I have a column with the prices of each product in the same row. What I need to do is take the value in the price column and insert it in a specific spot of a different cell (but still on the row).
A1 = <b></b>
B1 = 29.99
How would I get that price information between those two bold tags, and do this for all the rows I have that contain specific price info to that row?
I have a lot of HTML in the line I want to bring the price over to and I have tried the following formula at the beginning cell of A1 ="<b>"&B1&"</b>" but I get an error.
I am trying to concatenate the formula below so that the data lines up properly but I can't figure out if I use LEFT, RIGHT, LEN, REPT or what is the proper combination.
D12 in the formula is always 13 characters, after that I want three spaces and then the next data which will be a one to three digit number (FP12 is 0 to 100) then "% Cmpl" (without the quotes), then three spaces and the next data which will be one to three digit number (FU12 is X to XXX) then a space and "Hrs Rmng".
I count that I need the data to be a max of nine total characters for the second part of the formula (100% Comp) and 12 for the third part (XXX Hrs Rmng) plus the three spaces between.
I want the data to be right justified so if the data is less than 9 or 12 character respectively, I want to add the spaces to the front/left of the data (xxxxxxxxxxxxx-----0% Cmpl-----2 Hrs Rmng).
I am guessing that I can also just increase the number of characters to be added to the front of the data by three and then I can remove the &" "& part of my formula?
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I am trying to concatinate a cell to show both text and date (in format mmmm dd yyyy) using a vlookup from another worksheet.
Below is the coding I have in the cell: ="High Importance Request - effective " & VLOOKUP(1,'Fuel Surcharge Key Dates'!$A$6:$E$65536,2,FALSE)
The result I get in the cell from the formula is: I still get it to come out to 39327 instead of June 4 2007
The vlookup in the formula above retrieves a date that has a cell custom formatted to mmmm d, yyyy ... as this cell is not a date that is entered by a user but is further retrieved by another vlookup of dates. Even if I place the VLOOKUP in cell A3, reformat that cell to be a date and change the coding in the formula to: ="High Importance Request - effective " & A3. I still get it to come out to 39327 instead of June 4 2007
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like Variable 1 Variable 2 Variable 3
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 have been trying to use the concatenate function to join some text strings to a cell on a different sheet - From an input sheet -Sheet 1 named Input, to a Notification Form (Sheet 2). Although the Function Argument display tells me that it will display the result I want it actually displays just the formula. It's a very simple thing
Address 1 10 Downing Street Address 2 Westminster Address 3 London Postcode SW1A 1AA
I want displayed as: 10 Downing Street, Westminster, London, SW1A 1AA in a single merged cell. All I am getting on the Form is =concatenate(Input!c25," ",Input!c26," "Input!c27," "Input!c28). I feel sure that it is a very easy solution but I can't arrive at it!!
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 have a column that is giving unwanted value . dont know the reason as that excel file has been created by some other guy and I just started working on it .
My Question is how to move to 2 cells left(A for example) from from that unwanted value column. and check if A is equal to cell above it , means B Cell(Row above A but same column).
As my excel file is totally based on Forms, Macros, I am not quite familiar with macros.
Is there any way to put if condition in one cell (column) and drag it all the way down which should work for all the values in these 3 column.
And also if A=B then I want to make that unwanted value cell="".
I am using the find or search function but I need to read my text string from right to left (from the end of the string, instead of from the beggining). Is there a way to do this?
In a large database with a large portion of cells that contains a flag to the left of a number "<LD 2.0" what would be the quickest way to move the flag to the left of the number so that it reads "2.0 <LD". This seems like it should be simple to do. I would prefer not to have to use a second cell and formula to do the modification if that makes sense. I'd rather do a "Find and Replace" if possible.
I am attempting to utilize the Mid, Left, and Right functions to parse out data returned in a single cell. There are twelve months of data returned with the "title" listed after. I would like to be able to FIND the title and return a specific number of characters of data to the LEFT not the right (as Mid seems to do).
In a1 i have 100.888 and a2 122.222 and a3 122.555UK,(and so on) in column b i want just want the number and not the uk, i have tried =left(a1,7) which works until a3 then it give me characters 122.555 and not the number/value 122.55. I need it as a value to use the vlookup, how do i do it?
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.
I am concatenating a date with some text. However, after concatenating, the date becomes a text string. How do I preserve the date format? Pls see the attached file.
I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.
Example:
Assume row 1 and columns A, B, C, D.
496 | 6d7 | Minor | Ø.375 +/-.010
What I need this to read as one long string:
#496, 6d7, Minor, Ø.375 +/-.010
Adding the text in Red.
Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.
and totally working! but the problem is if the word "ink" in the left of the value --> 253.00 ink and the result is #NA, is there any way that i can get the value whether the word Ink is in the left side or right side of the value?
also bothered why is it if the word is not "ink" in the cell and return -> #value since i put ("") in the last part of If function(value if false)?
HTML Code: If Left(Range("H18"), 7) = A23C567 Or Left(Range("H18"), 7) = A65C321 Then ActiveSheet.Cells(6, 7).Value = "Business and Private Banking" End If
Im trying top use the above bit of code and it does not work when the criteria is Text , so if I simply change the above to =1234567 it works fine -- But I need it to be alpha numeric
I need assistance in shifting cells to left based on three (3) specific text values in column B. There are tons of data and extra lines and stuff but consistently these three (3) text values are consistently not aligned and I simply need to shift entire row 1 space to the left. I can do the long and tedious process of going through them all but is there a macro which I can play that does it instantly...
I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!! The formula I am trying to do is something like this...
In column S:S IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1" IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2" IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3" IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"
Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.
When placing a control on a worksheet, the text in the label,option box etc controls is vertical and I can't find a way of changing the direction to left to right. It doesn't happen on all workbooks. I can put the control on a "normal" workbook and then copy it to the "abnormal" workbook but I would like to know why/how I can change the direction of the text.
Under Tools/Options/International the text direction states left to right
