CONCATENATE Use LEFT, RIGHT Or REPT
Mar 28, 2008
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&" "&TEXT((FP12*100),0)&"% Cmpl"&" "&IF(FU12=1,FU12&" Hr Rmng",FU12&" Hrs Rmng"))
What I want to do is this.
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?
View 9 Replies
ADVERTISEMENT
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.
View 9 Replies
View Related
Oct 29, 2008
ok - I have numbers that need to be converted to 12-digit numbers with leading zeros if they are less than twelve digits. for example, 1234567 would turn into 000001234567 to have 12 digits. to do this, i use:
=rept(0,12-LEN(A1))&A1
additionally, i need to strip off the last three digits and replace them with three zeros. my example would now become 000001234000. assuming the result of my first formula (above) is in cell B1, i would use:
=INT(B1/1000)&"000"
Is there a way to combine these two functions into one formula to make this conversion process more painless? Or is there another formula/function I can use that I haven't thought of or do not know?
View 9 Replies
View Related
Jun 22, 2012
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
View 1 Replies
View Related
May 8, 2014
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
[Code].....
View 9 Replies
View Related
Feb 5, 2009
I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.
For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae
A B C
1='Base Case'!A15='Base Case'!D15='Base Case'!G15
I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.
However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.
View 2 Replies
View Related
Oct 6, 2007
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
View 5 Replies
View Related
Aug 11, 2013
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
View 9 Replies
View Related
Nov 19, 2009
I want to grab everything left of the last occurrence of "." in a string, and in the next cell everything right of the last occurrence of "."
so say the string is 111.111.1.222
column 1
111.111.1
column 2
222
my current code (which works, but its messy) for the first cell is
View 3 Replies
View Related
Sep 16, 2013
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="".
View 1 Replies
View Related
Jan 14, 2010
I have a set of data in an Excel 2007 spreadsheet and want to move it to Sheet2. I move all that I need except for one column which needs some work done to it and I keep receiving errors. Sheet1 Column 12 is comprised of six digits, space, six digits, space, and four more digits. What I need is the first twelve (12) digits, less the space, moved to column1 on Sheet2.
The code I am attaching does the following:
The year 2009 to Sheet2 Column2
Column6 + Column7 to Sheet2 Column3
Column8 + Column9 to Sheet2 Column4
Column6 + Column7 + Column8 + Column9 to Sheet2 Column5.
View 2 Replies
View Related
Jun 19, 2007
In cell A1, suppose I have a string "Forrest Gump", how can I get the "Forrest" part out using left() or any other function?
View 10 Replies
View Related
Jul 13, 2007
I have a cell and I only want to look at the word after the first 4 characters of every cell. I do not know the word size so right(...) didn't work for me, I just know the first 4 characters I do not need and the word follows.
View 9 Replies
View Related
Jul 19, 2007
How can I use Len and Left together to ensure a workbook name begins with Order?
I've tried using Left("Order", 5) and didn't work.
Len(Activeworkbook.Name, 5) didn't work either.
View 9 Replies
View Related
Jul 4, 2008
I have a table with a range of numbers inputted as text (ie. 200-400, 300-500, 65-90, etc.) that I am extracting. I made a formula that works for pulling out numbers if they are in the triple digit range but don't want to rejig if they go down to double-digits, or up to quad. (too tedious to change the right and left count function midway if data suddenly changes)
The values that I am extracting are separated by -'s and when i use the formula below i end up with negative numbers if one of the pairs is less than 3 digits (ie. 2)
=VALUE(RIGHT('NAIC Spread 2007'!D11,3))
how can i work in a Len function to deal with this issue?
View 9 Replies
View Related
Nov 26, 2008
Obscure lines of info which I'm trying to break up into pieces. Being able to find from the right instead of left would be REALLY handy. See attached example with details and example!
View 3 Replies
View Related
Feb 4, 2009
I have a simple VLOOKUP that I can't manage to give the right answer. The contditions must be 'FALSE' as the the stock report database has so many similar numbers in it.
View 2 Replies
View Related
Apr 6, 2009
i have this issue, i named column J. now it says instead of using Social Security numbers as a unique identifier, they are considering using an ID of the first 3 letters of the last name (L Name) followed by the first letter of the first name (F Name). If the last name is fewer than 3 characters, the letter Z replaces each missing character.
View 3 Replies
View Related
Sep 4, 2009
What is wrong with this formula? It results in #Value error.
View 3 Replies
View Related
Sep 20, 2009
I need a formula that can remove all characters to the right of "]" for example, if a1 = abcdefg]1234, I want b1 to return the value: 1234. I tried =RIGHT(a1,FIND("]",a1)-1), but that didn't really work. It returned: fg]1234
View 4 Replies
View Related
Mar 25, 2009
I've been trying to figure out how to tell my left footer to automatically use the value of whatever "L2" is. How do I do this?
View 7 Replies
View Related
Feb 9, 2010
I am trying to use the left function to include only the first account number, for example if a cell has "120122 280000" (there may be many spaces between the two numbers), I only want 120122.
So far my programming only returns the 280000 into the cell.
I am working with cell values in column "K".
View 6 Replies
View Related
Oct 16, 2007
Is it possible to make VLOOKUP look to the left?
Imagine following arrangement of data:
Address Name PIN
A-street Abe 4587
B-street Bob 8214
V-street Val 9657
I want to know the name of the person who has PIN 4587. Is it possible to do this without rearranging the columns?
View 9 Replies
View Related
Feb 27, 2007
Is there any auto sorting available to sort w/in rows left to right?
View 9 Replies
View Related
Mar 6, 2008
I have one field that contains a city, state and zip. I need to extract only the city. I need the proper function to ask Excel to go to the cell and return all the text beginning the first place on the left, continuing until it reaches a comma. The number of places in the city will always vary.
View 9 Replies
View Related
Mar 25, 2008
I ahve got the below range.what I want is if the first chracter starts with any of the below corresponnding value to be shown.I am using left and abs for this . but when the chracter stars with "N" or "E" . i am getting and error.
6Connect
NExplore
1Entry
3Connect
2Entry
5Live
EAchieve
7Live
8Live
9 connect
View 9 Replies
View Related
Nov 11, 2009
trying to return a value left of a column instead of from the right.
ex
A B C D
1103013v2200172b
I want to lookup a value in column C and return the value from column A
View 9 Replies
View Related
Feb 1, 2007
i know the forumla, using left and find to get the value of a cell to the left of the first space. (i.e if the cell said [url] click here then im looking for just [url]
however, when I try and use this in vba I cant get it to "find".
im trying to get the value of a textbox, before the first space
View 4 Replies
View Related
Jun 19, 2007
i am having one excel spreadsheet where there are data in matrix of 365 rows X n columns. columns are not fixed.(but will always be less than 125). now i want to add blank column after every column through VBA
e.g.
a--b--c--d--e (these are columns)
date--scrip1--scrip2--scrip3--scrip4
now i want data to be rearrange as
a--b--c--d--e--f--g--h--i
date--scrip1--<blank>--scrip2--<blank>--scrip3--<blank>--scrip4--<blank>
means one blank column after every scrip idea is to calculate %return in that new added blank column
View 4 Replies
View Related
Jul 10, 2007
What I need is to find a max value within a range and then tell me what the row value in Column A is. Usually use VLOOKUP for this but this doesn't like minus numbers.
I think it has something to do with Match and Offset but can't get it to work properly.
I have attached an example, where the max of the total is 4,435 and belongs to Steve but how do I get this to do using formulas
View 4 Replies
View Related