Can't Apply Text To Columns Delimited By Space?
Mar 1, 2013Why 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
ADVERTISEMENT
Convert Space-delimited Data To Columns
Jun 9, 2009I have a software package that requires serial number effectively data to be entered in a particular format.
As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic.
The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,
The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.
I would like the data to end up in two separate columns as shown in example 2.
Example 1 (Starting format)
* indicates space
- indicates a range, this needs to be separated into two separate columns
2252*2254*2256*2257*2259*2272*2274-2276*2278*2280*2282*2284*2286-2641*2643-2681*2683-2712*2714-2717*2719*2721*2724*2726*2727*2729* 2733*2735 *2738*2739*2746
Example 2 (Finished format ready to be paste into software package
2252
2254
2256
2257
2259
2272
2274 2276...............................
Importing Text Space Delimited Text File?
Jan 16, 2014I 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]....
Open Txt Files Delimited, Space
Sep 6, 2008On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:
"This file is not in a recognizable format" - I click OK
"Delimited or Fixed Width (default)" - I change to Delimited
"Delimiters listed with Tab as default" - I change to Space
I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.
Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.
I am using Excel 2000.
Covert Space Delimited String To Array
Apr 24, 2008I have two columns of data, one for Position, and one for description.
The positions are concatenated and separated by spaces. E.g. C1 C2 C3 for each Description. I need to create a separate row for each position
So I need to convert:
Pos Decscription
C1 C2 C3 Cap 100n 10% X7R 0805
to:
Pos Decscription
C1 Cap 100n 10% X7R 0805
C2 Cap 100n 10% X7R 0805
C3 Cap 100n 10% X7R 0805
Or in other words split a space delimited string into an array, so that I can loop through the array, and add new rows
Separate Space$ Delimited String Into Multiple Cells
Oct 13, 2009I am trying to split a string into separate cells. I have managed to generate the formula for the description and first dlr value in the string but I am have trouble figuring out how to build functions for the rest of the string.
Example attached.
Remove The Extra White Space Between Each Comma Delimited Digit
Oct 25, 2009This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345
My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:
"A B C 12345"
So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?
Locate Text String After First Space And Before Last Space
May 26, 2007I have been working on different formulas to return the text string between the first and last space and have been unsuccessful. Is this possible?
I have tried several combos or Left and Right, I have been able to get the values after the first space, and the values before the last space, but not between the spaces.
String: Y60
~C CULT NUCLEUS 3X2 SPRING WST BK XL
Desired results: D60
CULT NUCLEUS 3X2 SPRING WST BK
Remove All Text Left Of Space And The Space
Feb 10, 2007I have two words of differing character lengths separated by a space.
How can I remove the first word... essentially, all the charcters to the left of the space AND the space itself?
Remove Only One Space If There Is Space From The End Of Text
Apr 10, 2013I only want to remove one space at the end of my text within a cell, if there is a space.
Code:
Sub hth()
Dim c As Range
For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp))
c.Value = Trim(c.Value)
Next c
End Sub
Create Individual Comma Delimited Cells From Delimited Cell
May 30, 2008I 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.
Splitting Comma Delimited Numbers Into New Columns
Apr 20, 2009I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so
Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5
The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is
Card 1 / Cards 2 / Card 3
Player 1
Player 2
IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.
Text Delimited By Number
Apr 29, 2009I 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
Split Concatenated Delimited Cell Values Into Separate Columns
Jun 20, 2014Im 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
Transpose Data From Rows To Columns, Export To A Tab Delimited File
Mar 12, 2003I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.
There are no spaces between entries.
Count Delimited Text Within A Cell
Jun 30, 2014I 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?
Sorting Delimited Text Within Cell
Nov 8, 2009Lets 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
VBA To Read In Delimited Text File
May 21, 2014I'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.
Text File But Comma Delimited
Sep 28, 2007In 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.
Save As Text With Unicode And Tab Delimited
Oct 11, 2008In 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.
Transposing Delimited Text Files
Dec 18, 2008I 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
Importing Tab Delimited Text File
May 4, 2009am 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.
Saving Sheet As Tab Delimited Text
Jun 18, 2008I 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.........................
Converting Pipe Delimited Data String Into Separate Columns Using VBA Macro
Mar 6, 2014I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
VLookup And Update New Value In Delimited Text Having Alphanumeric
Dec 22, 2013We 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 :: Saving As Text Tab Delimited
Apr 18, 2013Excel 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.
Editing Contents Of A Text File Which Is Delimited
Aug 2, 2009I 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).
Excel - Default Tab Delimited Text File
Jul 14, 2014When 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?
Import/merge Tab Delimited Text Files
Sep 9, 2006is 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.
Saving A Worksheet As A Asterisk Delimited Text File?
Sep 19, 2013I 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?