I have multiple items (Country Names) in cells exported from another database with what appear to be delimiters (semicolons) that cannot be counted. I want to count the number of items (Countries) separated by the delimiters. Is there a simple way to do this?
Column 1 fields:
TAIWAN, PROVINCE OF CHINA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;
CANADA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;CANADA;
INDIA;BRAZIL;DENMARK;GREECE;POLAND;MOLDOVA, REPUBLIC OF;HUNGARY;UNITED STATES;UNITED KINGDOM;
Result needed in Column 2:
2
6
1
7
9
What is the formula needed to get the Column 2 result?
I've a word form that collects free text and tick boxes. I'm reading these into separate rows on an excel sheets. So far what works...
I've got excel vba converting the word to a delimited text file I've got excel vba to remove the erroneous rtns that people have entered into the free text that was things up.
In the free text there are commas entered as well as the tick boxes and this is a csv. Example "how do I do this, I don't know",1,1,0,1,"really seems to be a challenge!"
If I just use excel to open the text file then the columns work out ok and on a single row - I think the 'text qualifier' is playing a role here.
But I can't replicate this in VBA. If I record a macro it's a query table and I don't know how to amend the code to read into rows.
In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows. I would like to export the first three columns only (Column A, B and C). The TXT file should have the following: First line: "This file was exported from Excel" Second line: The value of cell B4 of the worksheet Third line: The value of cell B5 of the worksheet Fourth line: Today's date ( as 27/Sep/2007) Fifth line: No entries (Empty row) Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value. Seventh line: Cell A13 value, cell B13 value and cell c13 value. etc, until the end or selected amount of rows. The values of the different columns should be separated by commas Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789) Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890) Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
The text file therefor would look as follows:
This file was exported from Excel. Company ABC On this continent 27/Sep/2007
I tried to adapt jindon's code but no luck. I am currently doing it the long way. ='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12 This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.
In my excel file I have chinese and some other special characters and many cells have text with commas.
My problem starts when I try to save my file as a Text.
When I Save As with the Unicode option I can see the chinese characters in my text file but also I see a lot of quotes """ because I have commas in my xls file.
When I Save As with Tab Delimited option to solve the comma problem, the chinese characters become?
It seems that cannot be possible to do it manually cause there is no option to Save As with Unicode and Tab option together.
So I would deeply appreciate someone could give me a solution with VBA code to save my file as Text with Unicode and Tab Delimited option.
I am working with Excel 2003 which has a limit of 256 columns. I often import text files into excel using a very simple macro. I specify the text file's name and location in a cell and then run the macro - it opens the text file, copies the data and pastes it into the worksheet, then delimiting the columns using the semi-colons that seperated the data in the original text file.
However, I now have a text file that would convert into excel as around 1,000 columns and about 20 rows. This obviously causes a problem since my limit is 256 columns in Excel 2003.
Is there any way that Excel can transpose my data when it brings it into the spreadsheet from the text file so that I end up with 20 columns and 1,000 rows? Ideally I'd do this in VBA and add the code onto my existing little macro
am in a time crunch to write VBA code(not use the macro recorder) to do the following:
Input data from a tab delimited Text file into a pre-formatted sheet.
Each column of the text file is as follows (product code, buy/sell, quantity, price, date) Example row below.
YM Buy 50 12800 1/2/2008
Is there code I can use to do this? I need each individual piece of data to import into it's own cell while keeping the Row/column alignment of the original text document.
I have a workbook with sheets of data that should be imported into another system. So i would like some code that does the following:
1.) make a copy of worksheet x and paste (paste special) the values in a new worksheet. 2.) Delete row 1 in new worksheet (the labels) 3.) Ask the user for a file name and path and save the worksheet as a tab delimited txt file. 4.) Delete the worksheet created in 1.) 5.) Leave the user with the original .xls file I will assign this macro to a button so the experience for the user is: 1.) press button 2.) input file name and location and have a text file saved 3.) be left in the original xls document. I tried with the following code but get stuck with this error : "Run-time error 91: Objeck variable or with block variable not set."
Sub Export_x_DK() Dim w As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "Export_2023" Sheets("Depreciation x (DK)").Select Cells.Select.........................
Why can't I apply text functions on strings into the file? For example: can't apply "Text To Columns" delimited by space on "Status Entry Date" column.
We've got an end user who opens a .dat file which is delimited with semi-colons, replaces some text in a couple of columns then saves as a text tab delimited file. She swears that before today, the saved file would retain the semi-colons. Now she says it's not. she generated a new .dat file for me and recreated the process, with the resultant semicolon-free file. I asked her to generate another one and send it to me. I opened it first in notepad, and saw the semi-colons.
Opened it in excel (I'm using 2010 on win 7 professional), which saw it as a delimited file and asked me to specify the delimiter. It shows up fine, all the columns are righteous. I made no edits and saved as a text tab delimited, and that saved file had no semi-colons in it when I opened it. This seems normal to me; there's no semi-colons in the excel file, so why would there be in the text tab delimited file. I asked her coworker to generate another .dat from his pc and let me know what he sees, but he hasn't reported back yet.
I have a text file, which has a column of wrong data. I can change it manually by loading it into Excel but it is semi-colon delimited and I can't export to such a file.
I therefore, would like to write a VBA tool, which reads the file and edits the the value between the 5th and 6th semi-colon and will continue do this for each row (the new value is constant across the rows).
- contents of data.txt have range A1:Pn, where 'n' is last row, different for each file - found a previous thread very similar to what i wanted, but donno how to edit this to suit my purpose [Solved] Importing: Import many TXT files to singe workshee
BTW, does VBA recognise directory folder with unicode characters? I can rename my directory if it doesn't.
I am trying to save a worksheet as a txt file. The worksheet has a range of data with a variable amount of rows in column A only. It generally will look like:
where each row of data is in a single cell.Whenever I save it, however, it adds extra tab deliminations in each row after the data, and it adds rows with no data (sometimes just 1, sometimes many).
I am positive that no cells in columns B-n have data in them (even just a space) and no rows after the last intended have data.
Why are these extra rows and columns being inserted? How can I save a txt file that literally has no other spaces or rows or deliminations other than what's intended?
providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.
I am not sure if this is possible, but worth a shot. I am using a countif function to count a cells and I want it to count only if it has text, followed by a space then more text. I've been able to count the text only followed by a space, but cant figure in the more text part.
{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))} I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.
I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.
{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))} This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.
I've seen a lot of other posts on here regarding referencing a COUNTIF statement to a cell instead of a value (like this: =COUNTIF(B7:E7,"<=" & B10), but need to do it for text instead of numbers. This is basically what I've got:
A B C
Missy 342 Missy 4
[Code].....
Column B is created using a search function, and returning only the Missy or Mike, (using a search function, which I know how to do), and then I want the counts to populate in column C based on how many times either Missy or Mike pops up in the range.
Problem is, when I try and write a formula like =COUNTIF($D$2:$D$9,VALUE(E2)) it doesnt work, but if I put in =COUNTIF($D$2:$D$9,"*Missy*") it works (of course).
Is there any way to get it to reference the text in the cell since the text in the search criteria will constantly change?
Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg
Concatenated comma delimited data
Cust Name Cust Identifier Locations L Kim 543545 California,Chicago,Florida S David 31434 Maryland,Vermont,Maine D Bryant 572575 Texas,Oaklahoma,Nebraska
i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one
Customer name Customer Identifier Location L Kim 543545 California L Kim 543545 Chicago L Kim 543545 Florida S David 31434 Maryland S David 31434 Vermont S David 31434 Maine D Bryant 572575 Texas D Bryant 572575 Oaklahoma D Bryant 572575 Nebraska
A B Apples 1 Apples 1 Bananas 1 Carrots 0 Carrots 0 Donut 1 Donut 1 Donut 1 Donut 1 Egg 1 Fish 0
I would like a to create a function in one cell that counts the unique text values in column A, but only when there is a corresponding 1 in column B. So the answer in the above example would be 4 (Apples, Bananas, Donut, Egg).
