Free Microsoft Excel 2013 Quick Reference

Comma separator Results

This problem may be commented on this forum before, without my recognition. I have written a Macro to copy large amount of data from a third party application and paste this info into my spreadsheet. To locate the info in the third party application and place it correctly in my spreadsheet works, but the “Macro executers Paste function" interprets comma as thousand separator and not as decimal point as the third party format is. When I paste with the “manual paste, - Ctrl-V" function in Excel, it comes out right;

I have isolated the problem to the following test;

If I have a content in the clipboard like,

12,345
67,89

And opens an empty Excel worksheet and paste the content into A1:A1, that comes out nice.

However, if I record a Macro just to paste the clipboard content into the default pair of cells, it comes out like this,

12345
6789

Does anyone have a suggestion how to fix this?

Kind of a lengthy title, but here's the basic idea:

In one cell (we'll say A1), I have a list of countries. Each country is separated by a comma. One such example would be:
United States,Mexico,Canada

On another sheet within the workbook, I have an array from... lets say J1 to N5 with different countries in it:
Brazil United Kingdom Australia
United States Mexico Russia
Japan Korea etc.

I'm looking for two different types of single cell equations:
One that will create a single value of X for whenever a country from A1 matches ANY country in array J1:N5.
With the example above, if X = 5, then the result would be 5.

One that will create a sum value of XY for however many countries from A1 match any number of countries within the array J1:N5.
With the example above, if X=5, then the result would be 10.

Thank you in advance for your help~

I may have the terms wrong, but I require to build a series of drop down lists in various columns so each new cell presents me with a series of pre entered text only options to add into that cell. To complicate matters, the cell may require multiple entries, comma separated from the drop down list.

EG. a list containing 20 different entries, may require 4 of them in the one cell.

I have seen this functionality, but know not how to establish it in Excel 2008 for MAC. Can anyone assist please? Thank you!

Trebora

Hello,

I have a cell that is referenced to another sheet within Excel that is a merged cell of 5 rows and 5 columns. (i.e. ='First Sheet'!A1)

This cell contains information in information that is either 2 or 3 text characters long, some with a + sign (22+,555,RT+,KEW+)

I need to be able to separate the information by comma. But I need it to be able to allow me to edit the original cell in the First Sheet so that the changes made change the link as well. I have tried text to columns, but I can't figure out how to get it to work if there is a formula instead of values.

Next I need to be able to then reference what the + sign means (i.e. 22+ = 22,33,44,55,66,77,88,99)

Thank you for your help!

Hi, I have a spreadsheet with some cells having more than one line. I would like to parse the acronym from the beginning of each line and put it in a comma separated list in the next column.

Example of single line cell which I can parse:

        SOURCE            |   DESTINATION
_________________________________________
BE - Business Excellence  |      BE
_________________________________________
^ That uses the formula =LEFT(A2,SEARCH(" ",A2,1))

What I also need to do on the same sheet is to parse:

        SOURCE                             |    DESIRED RESULT
_________________________________________________________________
I - Oil & Gas and Industrial Applications  |
I4 - Gas Turbines                          |
I6 - Oil and Gas                           |
P - Fossil Power Generation, Products      |  I,I4,I6,P,P1,P2,P3
P1 - Steam Turbines                        |
P2 - Generators                            |
P3 - Gas Turbines                          |
_________________________________________________________________
I think my difficulty lies in detecting a newline character in a formula. Any insight on a possible formula that works with both scenarios would be greatly appreciated.

On my own system using Excel 2002 on Windows XP, when I open a .csv file it automatically selects commas as the delimiter and displays the content formatted. However when I send the same file to a colleague and she tries to open it it displays the file all in column A with the comma separators displayed.

Somehow I have Excel set up to assume commas and she does not (I guess it assumes tabs). Where do I change this setting to alwasy assume commas?

Thanks,

Peter

I have about 600 e-mail addresses, individually celled and I'm looking for a way to copy/paste them into a format that has them all listed out, separated by commas.

Here is the current format:
abc@abc.com
abc2@abc.com
abc3@abc.com

Here's what I need:
abc@abc.com,abc2@abc.com,abc3@abc.com

Can this be done?

Hi,

I have a formula that produces a number in cell A1. This is currently
£5,085,030, but can range anywhere from £1 to £999 million.

How do I incorporate this into a cell that will read:

"Split of £5,085,030, based on Build Costs".

?

At the moment, I have

="Split of £"&round(A1,0)& ", based on Build Costs"

But this does not give me the comma separators betwwen the millions,
thousands and hundreds. how do I get them in?

Many Thanks,

Gary Thomson.

Sorry, I'm re-posting this question because i actually forgot to check for
an answer. I apologize but now I really need this answer. Thanks for you
help.

is there a simple way of exporting a comma separated text file (*.csv) from
Excel with a choice of a single (') or double (") quote delimiter?
Thank you in advance.
G

I have some data in a Excel spreadsheet. There are 4 columns and the last column cell has multiple data separated by comma. I want to split them into different cells in the same column but Horizontally. Please see the attached for more details.

Appreciate if you could send me a macro for this.

Hi, does anyone have some code that could do the following? Column A is Invoice, column B is Serial:

BEFORE

INVOICE
SERIAL
1000 ABC1 ABC2 ABC3 2000 ABC4 3000 ABC5 ABC6
AFTER

INVOICE
SERIAL
1000 ABC1,ABC2,ABC3 2000 ABC4 3000 ABC5,ABC6
In short, group all serial numbers for an invoice on one line in column B beside the invoice, comma separated.

In the right hands this would be easy, in mine impossible!

Many thanks in advance.

Baz

Hi there,

I am listing all project information to a spread sheet. The spread sheet has three columns . Column A for project name , B for system impacts and C for Release dates.
We have 30 systems and a project may impact one or more systems. And a project has one or multiple release dates.

What I want is
Column B – when I click on a cell it should list all available system names and able to select multiple systems. All the selections should be in the same cell, separated by commas . Please see the attached.

Column C- when I click on a cell it should show a calendar and able to select multiple dates. All the selections should be in the same cell, separated by commas . Please see the attached. The dates must be in the format mentioned in the attached( Eg: 05-AUG-2011).
Please help me out on this

Hi everyone,

I have been trying to find information on this but haven't succeeded in all parts. Here is what I want to do in Excel (2003 compatible VBA).

I have an Excel workbook with 2 worksheets: SEG and IMPORT
SEG: contains formulas and VLOOKUPs to the Import sheet.
IMPORT: contains a text file (comma separated), and is not modified in any way from when it is opened.

I would like a macro to open a dialog window (asking me for the text file), then open the text file and copy it's contents into the IMPORT worksheet of the workbook.

Some issues:
the master Excel Workbook can have different names and be saved in different locations.The text file to be imported can also have different names (hence the dialog)
Does anybody have an idea how to achieve this or could lend me a hand?
Thanks in advance, attached a screenshot of the text file opened in Excel (just to give you an idea).

Best regards,
Titus

Hi All,
I could use a hand from somebody with more expertise on this one. I'm attaching a sample sheet (trying to, anyway) to help clarify a bit. Forgive my rudimentary vba

There are two sheets. Sheet 1 contains three columns. The first is a BBID (a numeric identifier). The second, which I don't need, is a company/index name. The third is a series of keywords, comma separated, used to describe that particular BBID.

On the Sheet 2, there is one column, which contains single keywords.

What I need to do is return every BBID that contains each of the keywords on sheet 2.

My macro, though not well done, does seem to work - but ultimately there will be close to 1 million rows of data on sheet 1, and 40,000 keywords to map, not the 10,000/9 I have in my sample. Wth my macro, this will take quite a while to run.

Any suggestions are greatly appreciated!

Here's my macro code:
Sub findmatchingkeywords()
Application.ScreenUpdating = False
Dim keyw As String
Dim bbid As String
'find last row with data
Sheets(1).Select
lastrow = Range("a1000000").End(xlUp).Offset(1, 0).Row

Sheets(2).Select
Range("a2").Select

'determine keyword and locate bbid's of any rows with matching data
Do Until ActiveCell = ""
    keyw = Trim(ActiveCell)
    Sheets(1).Select
    Range("C1").Select
    Do Until ActiveCell.Row = lastrow
        If InStr(ActiveCell, keyw) > 0 Then
            bbid = ActiveCell.Offset(0, -2)
            Sheets(2).Select
            Range("xfd" & ActiveCell.Row).End(xlToLeft).Offset(0, 1) = Trim(bbid)
            Sheets(1).Select
        End If
        ActiveCell.Offset(1, 0).Select
        Application.StatusBar = "Now on row:  " & ActiveCell.Row
    Loop
    
    Sheets(2).Select
    ActiveCell.Offset(1, 0).Select
Loop
If my attachment didn't come through this time, will try again momentarily!

Hi,
I can't figure this one out nor do I know the functions I should use (so I can't effectively search for a way to do it). I have one worksheet with data in columns; the columns alternate with text then an associated %. I am trying to search the % columns for IF < 95; If the % cell is less than 95% then alter the associated text cell (which is the same row, previous column). This is where it seems to get complicated: I need the text cell to be altered to show "unclassified_" & text from a different cell in the row. This different cell needs to be chosen based on its % too, where it will be the last cell, in the row, to have an associated % above 95%.
example of two rows (comma separated columns):
Bacteria,100,Firmicutes,100,Bacilli,92,Lactobacillus,42
Bacteria,100,Firmicutes,100,Bacilli,95,Enterococcus,82

They should end up as:
Bacteria,100,Firmicutes,100,unclassified_Firmicutes,92,unclassified_Firmicutes,42
Bacteria,100,Firmicutes,100,Bacilli,95,unclassified_Bacilli,82

Thanks for any pointers.

I want to delimit this into city, state, and zip.
Unfortunately there are sometimes more than 1 comma for example the last line in the below example.
As a result I want to delimit from the right. I was wondering if this is possible/ any other alternative to separate the data.

Matthews, TX 7508175081
Glen Lexington, TX 7524375243
San Felipe Houston, TX 77057
First Avenue, New York, NY 10016

Hello Forum members - I am not a programming expert but would appreciate any help on the following

In the attached spreadsheet I am trying to turn the 'Before' tab into the 'After' tab

The steps I want to complete are:

On the ‘Before’ tab
In column H there is a comma separated string of product numbers
Steps
1.Count the number of product numbers - 1 (number of commas)
2.Duplicate that number of rows below the current row
3.Transpose all those product numbers so one is in each of the rows created (in column H)
4.Duplicate those contents of column H into column A
5.Go to the next row, column H and repeat from step 1
6.Until reaching the end of the spreadsheet

The end results are shown on the ‘After’ tab

The number of products in column H will ALWAYS be >=1

I have a worksheet where i want to take all the pick tickets for a p.o and dump them into one cell with a comma separating each of them. I could use an AND formula, but the worksheet is a living document that changes daily and it will be used by someone who's not excel savvy. And more importantly, I want as much of the worksheet as possible to update on its own.

I need a formula in the cell to say "Dump all the pick tickets for a p.o into this cell and separate them with a comma"

I should also note that all of the pick tickets are going down the sheet in case that makes a difference.

I feel like it would be a vlookup and an array formula, but I cannot figure it out.

I've attached an example of what i want it to do. There's 3 tabs, the raw data which is what my pivot table will pull from, the Before Pivot which will show how it currently looks because I can't figure out the formula, and the After Pivot which will show what I want the end result to look like.

Hello All!
I have this situation that i have some data (different length but comma separated) in one cell, say C3. C3 is like this: C3="dog;cat;snace;etc". I wanna split all these content within C3 into different rows. Like this:D4=dog, D5=cat, D6=snace etc..
Could you help me out!
Thanks
Pomolo

I am trying to analyze Keno numbers. Following is a list of sample
draws. I have listed just 17 draws, it can be more than that.

DrawID,Date,N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N20
1,01/01/06,3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
2,01/02/06,3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
3,01/03/06,4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
4,01/04/06,4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,01/05/06,5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
6,01/06/06,6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
7,01/07/06,4,7,9,10,11,20,28,29,30,32,34,35,40,41,49,52,66,69,70,74
8,01/08/06,3,4,8,10,14,20,21,23,28,29,32,37,44,47,48,49,56,64,69,72
9,01/09/06,1,6,9,10,11,13,21,25,29,33,36,43,48,49,51,52,63,65,72,74
10,01/10/06,1,3,7,11,14,18,27,33,35,37,39,41,45,47,48,53,64,65,75,77
11,01/11/06,3,4,5,6,11,13,15,18,28,29,35,56,61,63,64,69,71,74,75,80
12,01/12/06,3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
13,01/13/06,3,13,15,21,24,27,28,35,47,48,49,54,56,57,63,72,75,76,77,79
14,01/14/06,4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
15,01/15/06,4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
16,01/16/06,8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
17,01/17/06,3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80

Problem : I want to list only those combinations that meets a given
criteria.
----------------------------------------------------------------------------------

I need a vba that gives three prompts to the user which asks for
Combinations, Matches and Frequency.

Lets say the user enters:
Combinations (C) : 5
Matches (M) : >=4
Frequency (F) : >=8

Considering the above criteria, the macro should list combinations of 5
numbers (C) out of which any 4 numbers (M) matches in more than or
equal to 8 draws (F).

For instance, have a look at the following combinations:

10, 21, 28, 47, 72 = Any 4 or more numbers from this combination
matched with draw numbers 2,8,12,13,14,15,16,17
10, 21, 47, 57, 72 = Any 4 or more numbers from this combination
matched with draw numbers 2,8,12,13,14,15,16,17
15, 21, 47, 64, 72 = Any 4 or more numbers from this combination
matched with draw numbers 2,3,5,8,13,14,15,16
21, 47, 57, 64, 72 = Any 4 or more numbers from this combination
matched with draw numbers 2,3,8,12,13,14,15,16
3, 10, 21, 47, 72 = Any 4 or more numbers from this combination matched
with draw numbers 2,8,12,13,14,15,16,17

I want the combinations listed in the following format

C1,C2,C3,C4,C5,Frq,dID1,dID2,dID3,dID4,dID5,dID6,dID7,dID8
----------------------------------------------------------
10,21,28,47,72,8,2,8,12,13,14,15,16,17
10,21,47,57,72,8,2,8,12,13,14,15,16,17
15,21,47,64,72,8,2,3,5,8,13,14,15,16
21,47,57,64,72,8,2,3,8,12,13,14,15,16
3,10,21,47,72,8,2,8,12,13,14,15,16,17

The combination first, then the frequency and their Draw ID numbers (In
different cells and not in comma separated values)

The logic I tried:
-----------------
I thought of making combinations drawwise. If I want to list
combinations of 5 numbers, then first combination would be 3,4,6,10,11.
Before listing this combination, check if it fulfils the criteria. If
yes, then list it else move to the next combinations. Do this for 15504
times [ =COMBIN(20,5) ] to ensure that we have analysed all possible
combinations of the first draw. Do the same thing for the next 16
draws. The challenge that I faced is that I can do it if the first
parameter Combinations (C) is known. The problem is that the
combinations that are being generated are user specific, it can be 2 or
as high as 10. This is where I am getting stuck. There are so many
loops involved that I am now confused and not getting correct results.
I would appreciate if anybody can help me with this. I hope I have
explained my problem well. Please let me know if any of you need any
clarification.

Optional: If possible, I would also want an indicator (somewhere within
any cell in the worksheet or on a userform) that will tell me what
percentage of the work is in progress in 00.00% format.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Few more examples that I made to verify accuracy of the macro results.
I did this manually which took 3 days and would like to automate this
process.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Example 1.
-----------
C: 2 M: =2 F:>=9

42 72

Example 2.
-----------
C: 3 M: >=2 F:>=13

4 35 47

Example 3.
-----------
C: 3 M: =3 F:>=6

10 21 47
10 21 72
10 47 72
21 47 72
4 10 21
4 47 72
47 64 72

Example 4.
-----------
C: 5 M: >=3 F:>=14

4 21 35 47 64

Example 5.
-----------
C: 5 M: >=4 F:>=8

10 21 28 47 72
10 21 47 57 72
15 21 47 64 72
21 47 57 64 72
3 10 21 47 72

Example 6.
-----------
C: 5 M: =5 F:>=4

10 21 47 64 72
3 4 10 21 69
4 10 21 47 72
4 11 35 69 74
9 10 21 47 72
9 10 21 72 74

Example 7.
-----------
C: 10 M: >=8 F:>=4

9 10 15 21 31 47 64 72 74 75
9 10 13 21 22 49 52 63 64 72
3 4 10 21 33 47 57 60 69 74
3 4 10 21 32 33 37 47 69 74
3 4 10 21 32 33 37 47 60 69
3 4 10 21 32 33 35 37 47 69
3 4 10 11 21 32 33 37 47 69

Example 8.
-----------
C: 10 M: >=7 F:>=7

3 4 7 10 21 47 57 64 72 74
Example 9.
-----------
C: 10 M: >=6 F:>=10

6 9 21 47 49 56 63 64 72 74
7 9 10 11 21 32 47 64 72 74
9 10 11 21 32 47 60 64 72 74
9 10 11 21 35 47 64 69 72 74
9 10 11 21 32 35 47 64 72 74
3 4 7 10 11 35 37 47 64 74
3 4 7 10 32 35 47 64 74 75
3 4 7 32 35 47 64 69 74 75
3 4 7 10 11 32 47 64 74 75
3 4 7 10 35 37 47 64 69 74
3 7 10 32 35 47 64 69 74 75
3 4 21 35 47 57 64 69 72 74

Example 10.
------------
C: 10 M: =10 F:>=2

3 4 10 11 21 33 35 60 69 74
4 7 10 21 33 37 47 57 69 75
4 7 9 10 11 32 35 41 69 74
4 6 13 15 35 56 63 64 74 75
4 6 9 12 15 35 47 56 63 72
4 6 9 10 15 21 31 47 72 74
3 10 11 21 32 33 35 60 69 74
3 7 11 33 35 37 41 47 64 75
3 7 10 21 33 37 47 57 69 75
3 7 10 11 35 47 57 60 64 72
3 4 11 21 32 33 35 60 69 74
3 4 10 21 33 37 47 57 69 75
3 4 10 21 32 37 47 64 69 72
3 4 7 10 21 33 37 47 57 69
3 4 10 11 32 33 35 60 69 74
4 9 10 21 33 41 47 57 60 74
3 4 10 11 21 32 35 60 69 74
3 4 10 11 21 32 33 60 69 74
3 4 10 11 21 32 33 35 69 74
3 4 10 11 21 32 33 35 60 74
3 4 10 11 21 32 33 35 60 69
3 4 7 21 33 37 47 57 69 75
3 4 7 10 33 37 47 57 69 75
3 4 7 10 21 37 47 57 69 75
3 4 7 10 21 33 47 57 69 75
3 4 7 10 21 33 37 57 69 75
3 4 7 10 21 33 37 47 69 75
3 4 7 10 21 33 37 47 57 75
3 4 10 21 32 33 35 60 69 74
6 9 12 15 21 31 47 64 74 75
10 16 28 47 51 52 55 64 71 72
9 13 21 22 31 49 52 63 64 75
9 10 21 33 41 47 57 60 72 74
9 10 12 21 22 47 49 52 64 72
6 13 21 22 31 49 52 63 64 75
6 9 21 22 31 49 52 63 64 75
6 9 13 22 31 49 52 63 64 75
6 9 13 21 31 49 52 63 64 75
6 9 13 21 22 49 52 63 64 75
6 9 13 21 22 31 52 63 64 75
6 9 13 21 22 31 49 63 64 75
6 9 13 21 22 31 49 52 64 75
4 7 32 37 47 57 60 64 72 74
6 9 13 21 22 31 49 52 63 64
4 9 10 21 33 41 47 57 60 72
6 9 10 13 21 49 52 63 72 74
4 15 42 45 47 57 60 68 72 74
4 10 21 33 41 47 57 60 72 74
4 10 11 21 32 33 35 60 69 74
4 9 21 33 41 47 57 60 72 74
4 9 10 33 41 47 57 60 72 74
4 9 10 21 41 47 57 60 72 74
4 9 10 21 35 47 64 72 74 75
4 9 10 21 33 47 57 60 72 74
4 9 10 21 33 41 57 60 72 74
4 9 10 21 33 41 47 60 72 74
4 9 10 21 33 41 47 57 72 74
13 15 21 35 47 49 56 63 72 75
6 9 13 21 22 31 49 52 63 75