Adding Character Then Concatenating Macro?
Mar 8, 2013
I'm looking for a quicker way to take a list of numbers and adding commas, then putting them in the same cell so I can input them into a web database much faster. I can do it long hand by using concatenate first on the list to add commas to each cell, then making another concatenate to put them all in one cell, but doing the latter takes quite a while because of having to input each cell into the formula. Is there a way I can combine the 2 into a macro to accomodate for any length of cells?
Example, I have this list
12
13
14
15
16
17
As my list of numbers, and I need it to look like this in one cell:
12,13,14,15,16,17
View 5 Replies
ADVERTISEMENT
Dec 17, 2008
A list of URL's in each row. Here's an example:
webmd.com/diet/rapid-weight-loss
webmd.com/food-recipes/features/10-everyday-super-foods?page=2
webmd.com/news/20000529/over-the-counter-drug-works-to-suppress-appetite
weightdepot.com/
weightloss.about.com/
weightloss.about.com/od/easyweightlosstips/
weightloss.about.com/od/southbeachdiet/a/aa060705a.htm
weightloss.com.au/
Here's what I'd like to accomplish: For each row that contains a URL that ends with a / I would like to remove it. If I use the find/replace feature, it would remove all /'s, which I don't want since a good majority of the URL's contain the /'s in various parts of the URL, such as in the example above. I'd also like to accomplish adding a / to the end of every URL for a list that does not have the /'s at the end.
View 5 Replies
View Related
Oct 12, 2009
I have a number in each cell 42189654 and I want to add a period in the 3 place. Example 42.189654. I cannot seem to get the correct formula.
View 5 Replies
View Related
Feb 20, 2009
I was able to get rid of the weird character [] with the =CLEAN(E11), where E11 had the problem character. but now where the [] used to be between to words the 2 words just show as 1. Example If the bad cell was just[]test the clean cell has justtest without a space between the 2 words. Since the spreadsheet is about 8,000 cells and has random [] characters is it possible to not just clean the bad characters, but add a space where they used to be all in one command?
View 9 Replies
View Related
Sep 3, 2013
This is my text:
Test Name1
Test Name2
Test Name3
Test Name4
Test Name5
Required result is:
Test Name1|Test Name2|Test Name3|Test Name4|Test Name5
View 8 Replies
View Related
Feb 11, 2010
I have the code below, which works fine apart from I get a little square coming up every time I run the code, as this gives you a line the Chr(10) has to be there. When I tale the Chr(10) out the little square disappears but then I don't get a new line. I have set the formatting to "wrap text" but it is still showing up. I am not quite sure where to go from here, I have also done a ="test"&CHAR(10)&"test" and that worked fine with no little square.
View 11 Replies
View Related
Jan 27, 2009
In column A if have strings like this: X-100-X10 or C-100-1X00 etc.
I need to change the X at the end of the string to "." e.g. X-100-.10 or C-100-1.00
Can't figure it out since there can be 1 "X" or more than 1 "X" and the second "X" that needs to get changed can be in a different location i.e. not alway 3 from the right or 7 from the left.
Is there some macro code I can use to make this change? Otherwise I have to change hundreds of these manually.
View 9 Replies
View Related
Jun 3, 2014
I am trying to create a button that when clicked it will add a character to the existing cell. Eg if cell had LDs and I click button I like it show LDs₂
The ₂ is character code 2082.
View 5 Replies
View Related
Oct 2, 2007
I'm trying to use Excel 2003 macros for the first time and am very frustrated by it's recorder function. I've used several standalone TSR macro recorders several years ago and they were much easier but certainly not as feature rich.
I have a spreadsheet that I've been using for a couple of years where I have manually entered data. My company has recently started to dump data into an Excel sheet but instead of pure numeric or date values being used they are extracting the values with the ' character starting the value. I want to strip this ' character.
What I do is select the cell I want to edit and invoke the macro. Then press {F2} to edit the contents of the cell, {Home} to move to the left, {Del} to delete the first character - the apostrophe, and {Enter} to complete the edit and move down one cell.
Using the recorder captures the following. Unfortunately it doesn't simply delete the first character, it copies the value of the previous cell to the next cell when I invoke the macro again.
View 10 Replies
View Related
Oct 1, 2013
I have a large numeric spreadsheet of water quality data for a range of metals. On occasions where a particular metal is below the limit of detection, the entry reads ".
View 4 Replies
View Related
Jun 10, 2009
i would like to use a macro to check whether the string present in a cell contains special characters like @,#,$,%,^,&,*. i need to do this using a macro. is there an in-built function to do this or is there any other way to do this.
View 9 Replies
View Related
Nov 27, 2012
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
View 1 Replies
View Related
May 18, 2008
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
View 5 Replies
View Related
Feb 4, 2014
where i copy up to 1000 products ID's like this: "1234567890 AA11111"(ofcourse there is allways differend code for every product its just a example :)) and paste it to exel, and the problem is i need only the 10-digit number (it's allways 10-digit number but the second one is sometimes diffrent) so it is possible to make makro that will remove every character after 10th character ?
View 6 Replies
View Related
Sep 7, 2012
Is iy possible to create a macro to add a character to a cell with existing text, such as:
Progressive Insurance $
Where the name is in "whatever" font,color,format, and then the macro could insert the $ as pictured above different font,color?
I have played around with trying to write, or copy as image...with no luck.
View 6 Replies
View Related
Aug 14, 2014
I am looking for a way to show the character location number of a text file, possibly in the first row or a macro that I can run at any given location that will give me the location # I am currently viewing.
Currently, when I open the file I can see the character # at the opening screen (see attached file) but they disappear when it actually converts. I would like to be able to keep the character location ruler once the file is opened in Excel so I don't have to manually count.
View 1 Replies
View Related
Sep 20, 2009
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
View 9 Replies
View Related
May 11, 2009
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 Related
Sep 21, 2009
I 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)
View 3 Replies
View Related
Nov 6, 2008
This 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 Related
Jul 24, 2009
For a visual aid I am using a msgbox to view my results. I have "Scenerio 1" and 2.
Snippet of code (CommandButton1):
View 3 Replies
View Related
Feb 12, 2006
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?
View 9 Replies
View Related
Apr 9, 2014
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?
View 2 Replies
View Related
Oct 5, 2007
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: ....
View 9 Replies
View Related
Jan 17, 2008
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..............
View 9 Replies
View Related
Apr 6, 2008
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
View 9 Replies
View Related
Nov 17, 2009
for i = 2 to i = 23
c.Offset(0, i).Value = combo_& i + 1 & .Value
next i
View 9 Replies
View Related
Apr 27, 2007
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
Aug 5, 2008
my 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
View 5 Replies
View Related
Dec 9, 2013
I have approx 1000 rows in my spreadsheet. Each row contains one column which is a number. What I would like to do is list all these numbers side by side with only a comma seperating them. Just say the first 3 rows have the following numbers 10003, 10056, 100039. I would like to get the numbers to appear in one cell like this: 10003,10056,100039.
View 3 Replies
View Related
