Lets say I have in column A a series of cells populated with words or phrases delimited by ;; and I want them sorted within the cell. I.e.
This:
Word ;; Another word ;; This is a phrase
Should become
Another word ;; This is a phrase ;; Word
I need to create a comma delimited list based on variable start and end values for each row.
StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008
I'm not a VBA expert, or I would have created a Do While or For Each loop.
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 found this code that import TAB delimited text file. I would like to import space delimited text file instead.
VB:
Option Explicit
Sub ReadTxtFiles()
Const conSpath As String = "C:"
[Code]....
I have a column of data consisting of Name and Number, for example:
ABC 123
ABC DEF 789
AB D EFG 567
Is there any solution that can parse the number from the Name, which returns:
ABC 123
ABC DEF 789
AB D EFG 567
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
123.4567, 23.7654, 123.4567
1234.5678, 123.4567, 987.6543
12345.6789, 6.0000, 2.9876
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.
View 3 Replies View RelatedWe want to insert new row by finding existing value of sheet1 and create new row with same content by replacing column B of sheet 2
Example1 : Sheet1 of Column A1 is having AHOPL5070B
Solution required : find AHOPL5070B from sheet2 of column B and update with inserting row with AHOPC5070B
Example for above:
429^TD^1^45055^1^D^11757^31032005^^AHOPL5070B
430^TD^1^45055^1^D^11757^31032005^^AHOPC5070B
MSN-TESTERmatchrow.xlsx
Excel 2007 on windows 7 professional.
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).
When I converted excel to text file via VB code, the default text file is tab delimited. Why is it so?
I've done:
Code:
Sub ConvertToText()
ActiveWorkbook.SaveAs Filename:="E:EXCELTEST.txt", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
End Sub
Can I change the default delimition to pipe, how can I do this? How can I make an excel file to pipe delimited text file in default?
is there any way for a worksheet to import and merge (append) tab delimited files from these directories:
C:TempmergeV¿ñdata.txt
C:TempmergeV¿ñ (2)data.txt
C:TempmergeV¿ñ (3)data.txt
C:TempmergeV¿ñ (4)data.txt
C:TempmergeV¿ñ (5)data.txt
- 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:
A*B*C
A*B*C*D*E*F*G*H*I*J
A*B*C*D*E*F*G*H*I*J
A*B*C*D*E*F*G*H*I*J
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?
Is there any way to write a macro that will take a specific sheet in a workbook and save it as a tab-delimited text file?
View 1 Replies View Relatedproviding 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.
View 14 Replies View RelatedMacro to copy data from Excel sheet and creates a Pipe delimited text file.
View 10 Replies View RelatedIs there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.
View 14 Replies View Related{= 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.
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
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.
The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."
I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!
how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.
Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.
Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.
To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.
I have a list of 2,000 plus numbers and the list contains numbers and text. When I sort or filter, the only option is sort A to Z (or Z to A), but the result is all of the numbers are sorted, then the numbers with letters are sorted. How can I sort both? I attached a excel example of what I am looking for.
View 4 Replies View RelatedWhat I have is a bunch of text files and 2 spreadsheets that sort the text files. Im not sure but I think there might be a better way but the problem is I dont know VB all that well. Can someone take some time and look over this stuff and let me know if there is actually a better way to make this happen. I also noticed that there are 2 txt file missing and not added in the sorted file (PS3_HIGH, PS3_URGENT)
The text files come from another network and are transferred manually every day to a folder. The purpose of the .xls files is so that someone can look at the counts and try to lower the numbers from day to day. If something is alarming frequently, the current files do not put that information together well. The numbers are still important, but is there a better way to do this sort in excel?
I have a worksheet where users enter an inspection number based on the order a property will be visited, based on the inspection number I want it to automatically enter the information into another worksheet that creates an itinerary which shows this order .
View 4 Replies View RelatedI have numbers like this and their sorting wrong. I want to sort in decending order by last digit to the right.
How it's sorting now: (sorting by first number sequence)
12/31/05a
12/31/05
11/01/06
10/31/06
I would like it to sort like this:
11/01/06
10/31/06
12/31/05a
12/31/05
These WERE text numbers that were formulated to read like dates. Is there any way to munipulate how to sort? I'm just looking to sort it like it was a date. I get text files in formats like this:
09062006 and I formulated them to read 09/06/06 like it would be a date.