LEN,LEFT, And Concatenation

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


ADVERTISEMENT

Excel 2010 :: Shifting Cells Left And Then Up If Left Cell Is Blank?

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

Autofill Formula To The Left (fill To The Left)

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

If Then Concatenation

Feb 26, 2009

I have a worksheet that has data in cells A3 - J3. One value will be placed in one of the cells directly below A3-J3. Is there a way to look at the cells A4-J4 and determin what cell has the value and then preform a concatenation on that cell pluss the one directly above it.

I have included a worksheet as an example.

View 11 Replies View Related

Vba Concatenation

Oct 7, 2009

why this code continues to give an error?

Sub TakeOut()
'
'
Windows("EXTRACT2.xls").Activate
Sheets("Sheet1").Select
For i = 1 To 100
Range("A" & i).Formula = "=CONCATENATE(""Trip "", ""=RIGHT('[EXTRACT.xls]Sheet1'!A & i,3)"", "" = "", '[EXTRACT.xls]Sheet1'!E & i)"
Range("A" & i).Value = Range("A" & i).Value
Next i

End Sub

View 9 Replies View Related

String Concatenation

Dec 2, 2008

I have two columns that consist of strings that I want to combine in a certain way. The first column has a string that consist of numbers and letters and the second column just has 3 letter codes. What I want to do is get rid of the letters in the numbers string and then add the 3 letter code on to the end of the numbers, and have this placed in a seperate column. For example: if column 1 contains "12F51Q" and column 2 contains "ABC" then the result would be "1251 ABC". Is this possible??

View 3 Replies View Related

Mass Concatenation

Mar 7, 2007

I have a table with 6 columns (with headers A -F) and 10 rows. There are values in each cell. I want to take a value from each of the 6 columns and concatinate them. I would like some code that would generate every possible resulting string and I would like the possiblities set out in one column.

Apart from the fact that it might be tricky enough to produce the code , by my calculations there will be 10 to the power of 6 possibilities and as far as I know there are only 65000 rows in my version of Excel. If they had to be put into 2 or 3 columns I would not mind

View 9 Replies View Related

Concatenation Aggravation

Jun 25, 2007

I've got a series of dates and times, each in a different cell, all in a row, and it looks like this:

19 Jun 07 17:30Z UNTIL 21 Jun 07 01:30Z

And I have a formula that says
=CONCATENATE("VALID TIME", E23,H23,J23,K23,N23)

However the result of the formula looks like

VALID TIME377900.729166666666666UNTIL377920.0625

Now this looks to me like something wrong with the formatting of the cell that has the formula, but I've tried different format with numbers, dats, times, text, etc and nothing reaclly changes.

View 9 Replies View Related

Another Reverse Concatenation

May 22, 2009

I need to reverse concatenate a column of addresses, but text to columns won't work. I'd like to have a formula that takes into account each of the following scenarios (basically any standard address you can think of):

102 Bart St
104 Homer Simpson Ave
106 US HWY BSN 805 W
108 N Springfield Rd

What I need is to have the result in four columns. The first field would the house number. The second column would be the prefix (direction) of the street name, IF PRESENT (so the first three examples would have no value in the second column, but the fourth one would have an "N". The FOURTH column would have the suffix, whether that is a street type (like Rd or Ave), or a post-directional like in the third example ("W"). The THIRD column would have everything else (whatever is between the prefix and the suffix). In other words, using the examples above (* indicating a new column):

102**Bart*St
104**Homer Simpson*Ave
106**US HWY BSN 805*W
108*N*Springfield*Rd

View 9 Replies View Related

Concatenation Using For Next Loop

Apr 26, 2006

I'm tring to concatenate the contents of column C with column H - the results to appear in column R. It will be an unknown and ever changing amount of rows.

I believe that it's the value for field 'NewField' that's causing most problems, but it could be other stuff.

Dim I As Integer
Dim LastRow As Integer
Dim NewField As String

LastRow = ActiveSheet. Range("A4").End(xlDown).Row

For I = 4 To LastRow

NewField = ("R" & First)

NewField.Value = (("C" & First) & ("H" & First))

Next I

View 5 Replies View Related

Vertical Concatenation With Tags

Mar 28, 2013

I have attached an excel sheet with the input and output required.

It is basically concatenation vertically with certain tags.

Is there a method by which i can achieve this. MACRO OR FORMULA??

Book_test.xls

View 3 Replies View Related

Automated Conditional Concatenation

Jan 11, 2010

I am trying to write a formula which will consolidate notes for shipping orders

Column A Column B

Ship A 12/10 Talked to customer
Ship B 12/11 phoned cust
Ship C
Ship A 12/11 Called again
Ship D
Ship B
Ship A 12/12 Shipped


I want the notes in column B to be consolidated together so I have the history for each customer in one cell ie

Ship A 12/10 Talked to customer, 12/11 Called again, 12/12shipped
Ship B

The spreadsheet is really large so I want the formula to look up the customer name and consolidate notes automatically

View 7 Replies View Related

Retain Formatting During Concatenation

Feb 4, 2009

I'm concatenating data in 5 cells (Author, Year, Title, City, Publisher), one of which is in italics (Title). When using concatenate (or &) the formatting is removed, and I need to be able to retain that formatting.

Example:
Col B
Aaron, M. (ed)
1999
The Body's Perilous Pleasures.
Endinburgh
Edinburgh University Press

View 4 Replies View Related

Using Link Formula And Concatenation

Apr 21, 2012

I have a formula in the target workbook in cell C3 =-'data.xls!'np2011'

However, when UI copy the formula to D3 , the formula remains as =-'data.xls!'np2011'

2011 after np is the year. The names in the source workbook have been named np2011, np2010, np2009 etc

np2011 is a range name in the source workbook. When I copy the formula to d3, it should change to -'data.xls!'np2010'

In the target workbook I have the years in cells C1 to L1. How can I get the formula to change when copying/ Alternatively can one concatenate the range name to C1 for eg such as ="-'data.xls!'np&c1&"' I have tried to do this, but cannot get it to work.

View 5 Replies View Related

Convert VLOOKUP With CONCATENATION In VBA

Aug 29, 2013

Im trying to convert the below code into VBA:

=VLOOKUP(CONCATENATE(A1,B1,C1),F1:Y4,20,0)

AreaOwner, Project_Name, ProjectCode are all defined variables

Sheets(A).Cells(6, 3).formula = VLookup(CONCATENATE(AreaOwner, Project_Name, ProjectCode), Sheets(b).Range("F1:Y10000"), 5, 0)

View 5 Replies View Related

String Concatenation Woes

Apr 8, 2008

I am trying to write a bit of code for the purpose of creating a dynamic email distibution list going to department managers in the event of an outage. There are checkboxes used to activate different headers to be added to the primary distribution list. I set the DIM statement variables to string type but it keeps telling me I need to set them as objects then if I switch to object type I cannot assign the email addresses in a string. I will list the section of my code giving me headaches: email addresses will be supressed to protect the innocent

Private Sub CommandButton1_Click()
Dim rng As Range
Dim DistList As String
Dim PriHeader As String
Dim Header1 As String
Dim Header2 As String
Dim Header3 As String
Dim Header4 As String
Dim OutApp As Object
Dim OutMail As Object

View 9 Replies View Related

Conditional Concatenation The Values

Nov 20, 2008

I have a big spreadsheet. I would like to concatenate the values in 4 columns, starting from right to left, separating each concatenated value with a " / ". One or more (or all) columns may be blank, and the " / " should only be inserted between two values.

This example makes it easier to understand...the first column shows the intended result.

Sheet1  ABCDE1ConcatenateLev1Lev2Lev3 Lev42XX   3Y / X XY 4     5Y Y  6Z   Z7Y / XX Y 8     9A / Z / C / YYCZA Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Conditional String Concatenation

May 29, 2003

(see spreadsheet below) ....

View 9 Replies View Related

Concatenation Of Cells Used To Reference

May 18, 2006

I need to reference a cell based on the results of two others - basically concatenate the two and then use the result.

A1 = G
A2 = 5

Then in B2, I need the value of the item in G5. So I need B2 to contain something like:

="[Value of A1]+[Value of A2]"
=G5

B2 now reflects the value in G5.

I know what I want to do, just can't get the syntax.

View 4 Replies View Related

Compare Worksheets For Concatenation

Oct 6, 2007

So far the code compares two worksheets of two different structures that have been merged into one workbook. It compares by the row ID number appearing in column B of worksheets 1 to the row ID number of column 3 worksheet 2- the aim being to concatinate with all names in worksheet 2 that have the same row id number as that of row id number in worksheet1- the concatination is output producing a list of names in column T in worksheet 1 -for what will be a later be part of a label mail list. The code mainly works great and does allow for when no matching row id found on second wroksheet as it just uses the name on the first worksheet - but the PROBLEM I am having is the code hits a bug /stops in cases when a single person on worksheet 1 also appears in worksheet 2 and there is other people on the 2nd worksheet with same row ID to concatinate with. So at this time the code hits a bug when it comes across one of these cases so what is needed is for a change in the code so in these cases then it will use for the output the name in the row of the first worksheet of the row id it was comparing.

To make this easier to understand sample test file is attached with code as below included- if you run the code it will stop but you will find output would have started to output to column T of worksheet 1 - but when it got to Sue Rubble it stopped - if you were to delete Sue Rubble from the 2nd worksheet the code would not bug. However the real data workbooks does have cases of single people who may or maynot also appear in the second worksheet - the code as well as what it can achieve so far does also need to allow for these other case as described by outputing the name appearing against the row id in the 1st worksheet.

Sub PharlapTheData()
Const TEST_COLUMN As Long = 3
Const NEW_COLUMN As Long = 4
Const NAME_COLUMN As Long = 4
Const TARGET_COLUMN As Long = 20

View 9 Replies View Related

Concatenation-copy Values To Another Sheet

Feb 9, 2009

I have a huge list that i need to copy over to another workbook but have a problem. the data i have is stored on 2 separate columns, A & B. I need both values to be combined into one cell and separated by a '/'. The CONCATENATE functi0n works perfectly, but when i copy these values across to the other sheet, of course i loose these becasue the original look up values for concatenation are elsewhere. How can i copy these concatenated values across to my other sheet?

View 5 Replies View Related

Selective Concatenation, If Text Is Present

Aug 18, 2009

I have a spreadsheet with many many columns...the top of each column has a string (it's a bird name). Below in the same column are various blank spaces or letters (defining species abundance, if there), example:....

View 2 Replies View Related

Evaluate Concatenation Formula As String?

Feb 19, 2012

I have the following in VBA:

Code:
ActiveCell.FormulaR1C1 = _
"=mod_a1&"" ""&mod_b1&"" ""&mod_c1&"" ""&mod_d1&"" ""&mod_e1&"" ""&mod_f1&"" ""&mod_f1&"" ""&mod_g1&"" ""&mod_h1&"" ""&TEXT(mod_i1,""m/dd/yyyy"")&"" ""&mod_j1&"" ""&TEXT(mod_k1,""h:mm:ss"")"

I only want the user to see the string, not the concatenation formula. So how do I get VBA to turn this formula into an actual string.

I have seen some functions using ByVal and Evaluate with this sort of thing but this is just different enough that I can't figure it out.

View 4 Replies View Related

Add Concatenation Of Two Columns To Pivot Table?

Jul 18, 2013

I have a large data set that I want to pivot on to produce various results. I can easily pivot on two columns to produce those Unique IDs where East AND West are "Yes" or "No." I want an easy to filter on results where either East OR West is "yes." I could do this with a joined (Concatenated column) such as East/West but was hoping there might be an easier solution built into Excel pivot feature.

Unique ID
East
West

1
Yes
No

2
Yes
Yes

3
No
Yes

4
Yes
No

5
No
No

View 2 Replies View Related

Conditional Concatenation Of Text Strings

Jul 17, 2007

I want to concatenate the text of several cells into one cell that satisfies some condition (simular to a SUMIF). For example:

1 AB
2 CD
3 DE

The formula I'm looking for should return ABDE in case of 1 and CD in case of 2.

View 6 Replies View Related

Find/Left Functions: Grab Everything Left Of The Last Occurrence Of "." In A String

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

Storing Result Of Concatenation Of String And Date And Using It

Feb 18, 2013

I have two cells. One of which contains a String and other contains a date value. I need to concatenate both and use it as a key for vlookup function.

Cells(x,y)= "=VLOOKUP($B7&TEXT($F6,"mm/dd/yyyy"),'Copy Data'!H2:J161,2,FALSE)"
OR
Cells(x,y)= "=VLOOKUP(CONCATENATION($B7&TEXT($F6,"mm/dd/yyyy")),'Copy Data'!H2:J161,2,FALSE)"

In my resultant cell formula looks like =VLOOKUP($B7&TEXT($F6,"mm/dd/yyyy"),'Data'!H2:J161,2,FALSE) itself and the expected result is not achieved.

Eg: B7=04152AB F6=21/12/2010 I need to merge both values and use it in vlookup for finding match from another sheet named 'Data'

View 2 Replies View Related

Combining Columns To Create New Rows Not Concatenation

May 22, 2014

RCA Page for forum.xlsx

I'm trying to get entries from one table to appear in another. The first table is like this:

DateDate
Customer Column1
Description Column1
Customer Column2
Customer Description2
Created By

The table I want to put the data into looks like this:
Date
Customer
Description
Created By

View 2 Replies View Related

Concatenation With The Desired Output Showing The Date

May 27, 2009

I'm trying to concatenate quite a few cells together. One of the items being included in the concatenation is a date, however when I do this the output is the numerical version of the date that Excel uses to mathematically calculate with. My question is how do I do a cell concatenation with the desired output showing the date, not the numerical equivalent used by Excel? Book1.xls attached is what I'm talking about.

View 2 Replies View Related

Concatenation Macro For Variable Rows And Columns?

Apr 12, 2012

Our company gets excel spreedsheets with UPC numbers. The numbers are divided in parts or columns. Sometimes it's in 2 columns; sometimes in 3; and sometimes in 4 like this:

A_____B_____C____D
023__14444__779__9
023__14442__789__7

I'd like to be able to concatenate the digits and have the entire UPC number appear in a blank column to the immediate right and go straight down the line and calculate them all. I figured out how to do this with separate macros for 2, 3, and 4 digits or columns.

Sub Combine_2_Part_UPC()
Do While ActiveCell ""
ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Is there a way I could do this with just one macro, by maybe selecting the digits of the first UPC number in the first row of the data to cue the macro in as to how many columns to process?

(The UPCs are in the midst of a lol of other data like product cost, so a macro probably wouldn't be able to figure out how many to calculate on its own.)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved