Join Text Based On Value Of Left Digits
Jul 5, 2007
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.
View 7 Replies
ADVERTISEMENT
Apr 2, 2009
I have four different cell.
Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.
Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.
View 7 Replies
View Related
Oct 20, 2009
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...
View 9 Replies
View Related
Feb 26, 2007
Column A2:A100 contains part description various len lengths max is 50 min is 22.
Column B2:B100 contains scrap qtys.
In column C2:C100 in need to join the text in A and B.
i want the scrap qty's all to be alligned i.e level, as column C is shown in a userformlist box.
meaning joining text/values would look like this (ignore full stops)
dispenser calsberg .........70
tap beer......................... 80
cowl................................100
instead of
dispenser calsberg 70
tap beer 80
cowl 100
View 9 Replies
View Related
Dec 5, 2006
I am trying to change the value in a cell by referenceing it to another sheet. My cell is: Today it is 33F, have a nice day. Now, 33F should be called from another cell and should be inserted in this new cell. If I change the 33F in the other cell this change should be reflected in my text file.
View 3 Replies
View Related
Dec 13, 2006
I have a multipage control on a userform. On Page 8 of that multipage, I have label1, part of that label is modified by other parts of the program. This is done by the program writing to a sheet of Data and then the program pulls that info into this label. My question is,what is the correct format to populate the label?
see below: This is what I have in my Userform_Activate and Userform_Initialize subs:
CurrentVersion = Sheets("Data"). Range("N2").Value
Label1.Caption = "This will allow you to change the Caption of the UserForms through out the operational program. Some UserForms cannot be changed. The ending of" + Chr(32) + Chr(34) + "Automatic 2005 V" + Chr(32) + CurrentVersion + Chr(34) + Chr(32) + " will be added to what you enter below as default."
CurrentVersion is pulled off of the datasheet which is a number like 5.9.8.2. however it errors "Run time error 13 - type mismatch" I can only suspect the label1.Caption needs something like: Multipage8.label1.caption =
View 2 Replies
View Related
Jan 10, 2007
I have a column with numbers in each cell. I need to add the same three letters before each of the numbers. Example:
number in cell: 25067 I need it: ABC25067
I did a macro for it but had to do each one individually. I imagine there is a simple formula to do this automatically but I don't know enough to figure it out (despite reading up on it for the last hour!)
View 9 Replies
View Related
Jun 30, 2014
Join an array with Text elements to create a string that can be Evaluated
So for instance if I have Array("A", "B", "C") and I want to evaluate("=({" & Join(array, ",") & "})="A)"). Is there any way to do this without having to loop or push to a Named array first? I'll even take this evaluate thing if I can do it with text and numbers
View 6 Replies
View Related
Jan 17, 2007
I am using the following formula to join several text strings together:
=CONCATENATE(B4,$A$1,C4,$A$1,D4,$A$1,E4,$A$1,F4)
(in the above $A$2 is just a few spaces in order to seperate the test)
I was wondering if there is a way of joing text strings that will align all info properly? Right now it just joiins them together but visually that is not really very appealing.
This is an example of what I am getting:
11 111 1111 11 111
22 2222 22 2222 222
333 33 3333 333 333
This is how I want it:
11 111 1111 11 111
22 2222 22 2222 222
333 33 3333 333 333
View 9 Replies
View Related
Oct 21, 2009
I am trying to append data in let's say column A:A with the variable text in J:J. The data in column A:A will look like this /dir/dir/dir/<from this point is where I need the data appended>.
View 4 Replies
View Related
Oct 27, 2006
For the following formula:
=”TEXT1”&”TEXT2”
The result is:
TEXT1TEXT2
Is there any kind of modifier that I can use to force the output to multiple lines, i.e.:
TEXT1
TEXT2
If this wasn’t a formula I could just use ALT+ENTER. Wrapping the text will not work because I need fixed break points not dependent on the text box width.
View 5 Replies
View Related
May 15, 2007
I am trying to reference text and another document. The text I want is " stock option expense negatively impacted 1Q:07 by:" and the number i want it to bring back is currently in the model as a percentage. However I would like to bring it back in basis points which would be the percentage times 10,000. So if its in the model as 0.4%, I'd like to bring it back as 40 bps. Here is the formula I am using that is insufficient.
="Stock option expense negatively impacted 1Q:07 by: " & TEXT([FOSL2.xls]QTR!$BK$34,"0") & " bps."
View 6 Replies
View Related
Jul 2, 2008
I had a hard time to put a suitable title to this query, not sure if above is good enough for my query. I am entering combination of text and number in a cell using a formula. I am wondering if it is possible to add formula which can format number such that it is displayed in accounting format. Example:
Expected output = Trial 1,000
Using this formula
="Trial "&Sheet1!A1
where A1 in Sheet1 = 1000
Output is Trial 1000
View 6 Replies
View Related
Apr 27, 2009
i know this is easy but i don't have an idea how to do it. I have dates written on column A1:A7 like
29-Mar-09
30-Mar-09
31-Mar-09
1-Apr-09
2-Apr-09
3-Apr-09
4-Apr-09
what I would like to do is how can I combine 29-Mar-09 and 4-Apr-09 into one cell...I would like the output to be placed in cell A8 appear as "29-Mar-09 to 4-Apr-09".
View 4 Replies
View Related
Nov 19, 2013
I am trying to join text in two different cells using concatenate function / & operator In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?
View 4 Replies
View Related
Oct 16, 2006
I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".
View 4 Replies
View Related
Jul 25, 2007
I'm trying to convert a set of numbers between 1 and 9999 into text strings, with the number at the end as a four digit string, so 1 becomes "COLUMN0001", 123 becomes "COLUMN0123", etc. In excel, it would be easy - I would just use the text function and convert the number to a string and append it to the word COLUMN, but as far as I know, there isn't an equivalent function in excel and running excel functions in VBA is notoriously slow (especially as I will need to do this about 360,000 times).
View 4 Replies
View Related
Jan 17, 2014
I've 2 lists that contain similar info but 1 of the lists holds info I need to join.
I've a unique case sensitive id that I'd like to use to join the lists.
I usually use VLOOKUP but it is case insensitive and I cannot understand how to make it case sensitive.
The 2 lists are in 2 separate worksheets as they each hold a lot of data.
View 3 Replies
View Related
May 13, 2006
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet
2. repreat until all rows in worksheet 1 are read
3. save worksheet 3
View 3 Replies
View Related
Oct 10, 2006
Trying to build array elements from a list
19909
19953
etc
My thought is in helper column to concatenate the quotes and comma "xxxxx", Then I will copy paste special transpose and should have an array list
I tried ="""&A2&"","
Returns
"&A2&",
But I need it to return "19909",
View 4 Replies
View Related
Jan 11, 2007
This is not related to macro - it's an excel question regarding wrapped-around text within a cell (i.e.: text brought onto next line by <Alt>+<Enter>)...
SayI have the following in a cell :
Car<Alt>+<Enter>
Truck<Alt>+<Enter>
Van
and say I want to add a figure (in this example 1A) to the first part of the cell and then another to the second line of wrapped text and so on. How can I split the cell (or lookup the first 'wrapped' line) so that I can then perform changes (using formulas with '&' to combine etc or whatever) so that in the end cell have the following:
Car 1A<Alt>+<Enter>
Truck 2C<Alt>+<Enter>
Van 1B
View 7 Replies
View Related
Jul 20, 2007
Looking for formula with if then statement that involves a nested formula or concatenation. I need a formula that says: If cell A reads exactly ABCD then concatenate cell A with cell C. Very basic to most of you gurus but I'm fairly new with formulas.
View 3 Replies
View Related
Oct 16, 2007
I am trying to find a way to create a macro that will take the data selected from the combo box and concatenate it with the data found in cells.
For Example:
Combo Box Selection - "Test"
ID (Column A) - Before
12345
98765
99999
55555
empty cell
23232
ID (Column A) - After
12345 Test
98765 Test
99999 Test
55555 Test
empty cell
23232
View 9 Replies
View Related
Jan 11, 2010
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!!
View 3 Replies
View Related
May 16, 2013
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 have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
View 3 Replies
View Related
Feb 9, 2009
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".
I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)
I understand how to use IF and substitute. its the 7 numbers part i am stuck on.
I could do it in access with the wildcards but excel is different.
View 14 Replies
View Related
Nov 13, 2006
I have a string in a cell that contains a name, a 3-4 character alpanumeric code and then a 4-6 digit number, each separated by a space. An example or two:
J Bloggs SPEC 123456
J Smith AG06 9364
J & K Brown SWP 358686
I need to be able to have in a new cell the 4-6 digit number at the right of the cell. give a formula that will separate the data I need? I've tried numerous ways without success.
View 4 Replies
View Related
Oct 31, 2008
i need to format my numbers in the following format
10,00,000.00
the first three digits will be separated and then subsequently 2 digits
View 2 Replies
View Related
Jul 9, 2009
I know how to remove the first part of a text, but not the end. I have a very large amount of data in which the first column contains a product description and then the UPC code. I need to isolate the product name from the UPC code into 2 columns. I used =RIGHT(B2,14) to isolate the UPC, but since the length of the product name varies, I'm not sure how to isolate the product name. I have looked at using the TRIM and TRUNC functions without any luck. 'Text to columns' doesn't work because I have multiple spaces, 'Text to columns' would work if I could used 2 digits as the delimiter, but the function doesn't allow that.
Can someone please suggest a function or formula for this?
Here is an examples of how the text appears in the field. (Note: I only need to cut off the UPC code, I need the measure information to stay).
some really long product name 12 oz 5 bag 09-99999-99999
another shorter name 2 oz 09-99999-99998
View 6 Replies
View Related
Sep 14, 2009
I have a text string with digits in the þýmiddle in one þýcolumn.ýþýþ
I want to separate the text from the digits to different þýcolumnsýþ.
View 9 Replies
View Related
