I have many workbooks where Cols B and C and G and H all need to be trimmed of extra spaces, then concatenate B and C into Col D with a space between, same for G and H into col I. (If you hadn't guessed, these are first name and last name columns which need to be combined into one name.)
I have been doing this manually, inserting extra columns, copy paste special values, then concatenate, copy paste special values?
I have more than 30 Columns. I want to concatenate all these columns and put the value in one cell. When I do it, Exccel gives me an error, "You have entered too many arguments for this function"
View 4 Replies View RelatedThis is the concatenation macro I have for 2 columns. I want to make this more general in that I would like the program to insert a new column to the right and concatenate the 2 columns based on the current cell the cursor is on and the one adjacent to the left (-1). The loop works fine, the modification I need is for the following three (3) lines.
View 2 Replies View Relatedmy problem is that i have a huge number of rows and columns, what i want is to concatenate the data in all the columns to the data in the first one, but separately, this is really complicated, so let us assume that i have the following table
-----A-----B-----C-----D-----E
1 ---98---13----17----19----44
2 ---94---18----20----23
3 ---19---22----245---45----62
so after concatenating the value in A to all the values in the other columns and put the result in a new column we get the following output
-----A
1---- 9813
2---- 9817
3---- 9819
4---- 9844
5---- 9418
6---- 9420
7---- 9423
8---- 1922
9---- 19245
10--- 1945
11--- 1962
also we need to take in consideration the empty cells as they must be skipped during the process, also find attached my Excel sheet that needs the modification
This should be easy, I have a column of First Names, a column of second names and I wish to create a new column with First Name , space, Last name.
I can create a cell specific code such as
= CONCATENATE(B2," ",C2)
However I want to make this into a macro to run on any file that I open for every entry in the columns (I export a file weekly and need to run it each time).
My macro and VBA skills are very basic!
I receive a report which, amongst the other columns of data, has 7 specific columns with four digit codes on each row. I would like to find a way to concatenate these 7 columns into Column A, inserting a - between each code and adding -uu at the end of the last (7th) code.
I wouldn't be requesting assistance at all with this if the codes were always in the same column. Sometimes, they move 1 or 2 columns left or right depending on the additional (unwanted) data in the columns in-between. The column names, however, never change: Code 1, Code 2, Code 3, Code 4, Code 5, Code 6, Code 7
I was wondering if there was a way to still concatenate these columns into Column A but also take into account that the columns shift...
I have just imported alot of data into excel which i was wondering if theres a way of trimming
theres a column where all the cell values end in ".GB" or ".ES" basically a "." then 2 letters. Is there a formula to trim this off?
I've got a problem with a report that we need to manipulate in Excel. the report details the man hours each of our analysts has spent on calls and reports in the following format HH:MM:SS. However, the report comes out with a space after 'SS', so I need to trim this in order for excel to calculate total and average fix times. Cells are formatted to custom [H]:MM:SS.
With over 200 records requiring trimming per report, is there a formula that I can apply to this? I have tried =trim("Cell number") but this doesn't work.
I need to write a small macro (I assume it is small) that will go through each cell on a worksheet and in each cell, go to the first blank on the right and delete that blank and everything beyond that point. I can write a macro that trims at a certain point, but I am not sure how to have the macro find a blank spot that changes in each cell.
So just to be clear, say the macro goes to A1 and finds a word and several null spaces beyond it, like "Barn is red." The macro would go and leave just "Barn" behind. If A2, it would find "Dogs are hairy." It would leave just "Dogs" behind. Can someone help me with something like that. It has to be a macro rather than an equation as we do not want formula's int he cells.
Cell E1 having 150 characters (letters), I want to trim first 45 characters in cell E1 and place it in cell B2 and remaining characters in cell E1 (ie. from 46th character till end) and place it in cell A3.
View 1 Replies View RelatedLet's say in a cell i have the text ABCD/0123/01234 or ABC/0123/012345
How would i go about extracting whatever is inbetween the / ? i.e. 0123 in both examples?
Right now I have this code:
Code:
Set rng = Range(nm.Name)
On Error Resume Next
For Each Dn In rng
If rng.Value "" Then
Dmin = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(0), """", "")))
Dmax = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(1), """", "")))
If InStr(Dn, "-") Then
temp1 = Val(Trim(Replace(Split(Dn, "-")(0), """", "")))
temp2 = Val(Trim(Replace(Split(Dn, "-")(0), """", "")))
Dn.Interior.ColorIndex = IIf(temp1 >= Dmin And temp1 = Dmin And temp2 = Dmin And temp
I have import the database from SQL server, all the records imported begins with a blank space and they can not be trimmed using Trim function. Is there any smart solution ? I prefer to get VBA code to solve the problem.
View 9 Replies View RelatedI notice that my csv list of addresses from the northeast area is trimming a lot of zip codes that atart with 0's. this is probably also happening on the address columns for some addresses or box numbers with leading 0s. is there any way to turn this function off?
View 12 Replies View RelatedI have a credit card number problem where I am trying to trim the data so that I get rid of the spaces between the numbers (number sequence look like this: 5999 9999 9999 9999. This is currently formatted as text, and I would like to maintain this as text and not convert it to a number. The trim function can't seem to get rid of the spaces.
View 4 Replies View RelatedHow to trim when there are many lines in cell. The normal trim works only when we have one line in a cell.
View 9 Replies View RelatedIn the attached workbook I have groups of numbers in Col G
In col H I would like to trim col G to just one each of the numbers where there are duplicates (as shown).
Each group of numbers will have a blank row between.
I have a scatter chart that is populated from the values on a worksheet. The series formula looks like this:
=SERIES(,{2.36655521392822,2.54071712493896,2.70576763153076,2.8779809474945,3.04913735389709,3.2250 7190704345,.........},{5,5.09999990463256,5.19999980926513,5.30000019073486,5.39923381805419,5.5,5.5 995168685913,5.69999980926513,........},1)
I really don't need all of those extra digits, at most 2 significant digits is all I need. I wrote a piece of code that looks like this to shorten each number but it is not working. The code runs and in the watch window I can see that each digit is indeed being trimmed and converted back to a number (vs. a string) but the chart series formula still carries all of those extra digits. What am I doing wrong here. The code I wrote is below.
i m looping through each cell in the range A5:G11 and deleting the last part, "',[ABSMacro.xlsm]Region Breakdown'!$S$1:$S$64999" of the formula in each cell.
View 2 Replies View RelatedNeed to trimming certain information contained in 1 cell and putting them each in a column. I could do left, mid & right but this time there is inconsistency in the content of the cell.
I've attached a file which will further give you an idea exactly what I want (formula to be in Column M to P).
I am moving data from Sheet2 to Sheet3.
View 5 Replies View RelatedI have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.
I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)
For a visual aid I am using a msgbox to view my results. I have "Scenerio 1" and 2.
Snippet of code (CommandButton1):
I have a list of about 1,500 email addresses. To send an email I need to have a comma between each entry. I know I can use the formula =(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the 2 strings, but is there a formula to automatication do it for A1 - A1500???
Or is there an entirely different approach I should consider?
I have a table where a field is called "Expected conversions at [X%]".
X% is a monthly calculation.
So to populate the table row name I tried ="Expected Conversions At"&" "&C28 where C28 refers to a percentage calculated elsewhere in the sheet.
Cell C28 is formated to 1 decimal place. But when I refer to it in this manner it displays to many decimal places.
The result:
Expected Conversions At X.XXXXXXXXXXX
It should say:
Expected Conversions At X.X%
How do I do this so as the % is dynamic and auto updates each month?
I've got a spreadsheet that is being somewhat auto-generated from another program that extracts four different lines of text and puts them into separate cells (Starting at W30, X30, Y30 AND Z30). I believe there are 15 possible combinations. What I would like to end up with is each cell concatenated with " - " separating each one, but ONLY if there is text there to separate.
The function code I have so far gets me very close to what I want, but there are a couple of small issues remaining. See Red Bolded Text.
Here's what I've got so far.
Code: ....
I need to combine data from two adjacent columns into one in a condensed version of a spreadsheet. As the spreadsheet itself is quite big (over 20,000 rows) I was wondering if there was a quick and efficient way to doing this.
Here is a sample of what I'm working on (I know this would be better off as an image but unfortunately Photobucket is blocked at work):
Contract: Apr-2007 4000 Calls
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000
20-Apr-07Apr-074000
Contract: Apr-2007 4000 Puts
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000..............
I'm trying to create a model for woodworking where I can enter fractional wood sizes and have Excel create panel sizes and board lengths for cabinetry. I can easily get the inputs formatted in fractions, but then I'd like to have a concatenation formula that joins two fractions and displays a panel size as a fraction. However, when I try to do this, the concatenation formula changes the fractions to decimals.
Example: cell A1 is 3/4" formatted as fraction. Cell A2 is 1 1/2" formatted as fraction. Let's say I want a to concatenate so that A3 reads
3/4 x 1 1/2
But when I use concatenate (A1," x ",A2) the answer in A3 reads
.75 x 1.5
for i = 2 to i = 23
c.Offset(0, i).Value = combo_& i + 1 & .Value
next i
I have 1 work book with 2 work sheets. I need the data from 1 cell on the first sheet to be merged with a cell on the second sheet, ( I could also do with this having a line being inserted) can this be done in the same cell or does the merge command only work into a new cell?
View 3 Replies View Related