I am working with 300k records and would like to export to statistics software that accepts special character delimited. I would like to use the character | for the delimitaed process because looking through the excel file i see there are commas and extra tabs in some cells so using those characters will screw up my data.
View 9 RepliesTo 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
I 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.
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
[URL]
just had 11,000 customer account arrive and they are all messed up!
I am working through all the email address's and lots are not valid ones, is there a way i can make some sort of rule that willl do the following
If ther is no @ symbol, in Cells A ( any of A ) it drops them to the bottom of the list, or anything that just splits them up
Eg
fWGHFUI@JHEGF.COM
EFHWHEF@WEGFIW.COM
EKFGWIGF@YTRQUH.CO.UK
HJGIWEROPQWRP@WEYIO.COM
ASFHWIEGFIUQWEFH.COM
EHFIOQH12.CO.UK
This would split the bottom 2 email address away from the others as they dont have the @ symbol!
I have sentences which contains special characters (mentioned below) in a single column. I need to remove all special characters other than space. Could anyone help me on this...
!@#$%^&*()-_=+{}[]|~`?/,:;"
I have a userform with a textbox and an OK button and having clicked OK a particualar sheet is given the name in the textbox. If the character '/' is included in the textbox a standard Excel message appears explaining that this character cannot be used. On clicking 'END' on the message the userform automatically disappears and I have to close the file and reopen to end another name.
Is there any way that I could introducing my own basic messagebox which says 'The characters /, ? etc cannot be used' and on clicking OK the userform stays and you can try again.
I'm working again with a large Inventory Database dump into a workbook and in the past the company appended the * character to designate an updated price on an item(column C). Its rocking my world to sort through things with this character as you can tell. I'm trying to find rows that column C has a * in, cut, and paste them into another sheet called "Updated". I would even accept just how I'd do a simple "find/replace all" on that character and then I can just update my macro and be on my way Anyway this is the macro thus far and as you can see it would just cut all rows and paste them to "Updated".
Sub Updated()
With Worksheets(1).Columns("C")
Set c = .Find("*", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Cut Destination:=Worksheets("Updated"). Range("A" & Worksheets("Updated").Range("A65536").End(xlUp).Row + 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
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
I'm looking for a VBA Macro that will do the following:
1. Count number of cells in a given row that contain special character "*"
2. If there is an asterisk in a given row, return the column header(s) contained in row 1 of the spreadsheet that correspond to each instance of cell(s) that contains the "*".
Is there a way to do this? I've attached an example of what I'm talking about, (Column A shows the desired result.)
Formula Question.xlsx
I have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
I want to select email addresses in a particular cell. Since every email address will contain @ so I want to extract the email address from the cell on the basis os all the characters to be selected on the left & right of the '@' before a space is encountered.
Eg
If the cell contains the below values:
91 121 5158123 / 5159123, 011 5103710
91 999 999 1123
info@designs.com
I only want the email address to extracted to the next cell. i.e. info@designs.com
want a way to find out any special character in text file (.txt).
The .txt file I use is very large about 100 mb to 1GB. I need to find a way to write vba code that asks for input text file and the validates it and gives the message that following special characters are present in the file. Also, it gives their column number and row no's, where they are located.
The characters which needs not treated as special characters are numbers (0-9), alpha (A-Z) and special characters (@,-,%,$,+,=).
I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.
I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.
If I can determine the position of the last occurance of the special character, I could use the LEFT function.
The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.
Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.
There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.
I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.
i have a device that produce its data as following:
NTFLog_D2013-02-12_T104016.csv
I need to divide the single column to 7 column as separated by semicolon ";"
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 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.
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
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.
Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}
I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?
My formulaic solution is thus: .....
i have a tab delimited file that desperately needs cleaning up.
i would like this:
word1 def1
word1 def2
word1 def3
word2 def1a
word2 def1b
to look like this
word1 def1, def2, def3.
word2 def1, def2.
my problem is this dictionary goes on and on, and i would just like a simple way to find these duplicate entries and consolidate them into the form i showed above
I attached a txt file of a report we get out of our accounting system. It is not tab deliminated, when I open it in excel, each whole row is contained in 1 cell instead of being broke up into columns. Im not sure how to convert it, im sure it possible using vb or something?
View 3 Replies View RelatedI have a worksheet that has around 2000 rows each containing data as shown in the attached sample sheet. I want to replace all the squares (is this some form of delimiting or seperating character inserted as the data was imported?) with a space and leave the other data intact.
I've tried find & replace to do this without success, although editing each individual cell works. Please let me know as I need a way to do this for all cells at once as it would take an age to do each cell indivudually.
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
Figured out how to export to Pipe Delimited through coding but now I am faced with 2 complications.
1) The column order in the EXCEL file is not the Order I want it to Export in. (i.e. COB Date maybe in Column 'I' in the Excel File but when I export it into .txt I need it to be in the First Column)
2) I need to have the header name Changed in the .txt file (i.e. COB Date is the name in the EXCEL file but I need it to show as cob_date in the .txt file)
how to write content of an array and a tab to a file. I have all the file handling working, my questions is how to do the actual write. The items in the array are all text strings but this doesnt work
Write #1 myRng(i,j) & vbTab
with the obvious assumptions. I just want to write the array value and a tab to my tab-delimited file in the next location. tia.
I am trying to use VBA to manage invoice generation via an online service. The service is set up in such a way that the process cannot be made completely automatic, but I am trying to reduce it to as few manual steps as possible. Hence I am opening the Web Browser control in an Excel userform to streamline and guide the user's process. The invoice is generated as a Tab-delimited TXT file. I don't want to save the file to disk, and selecting 'Open' opens the file as a web page, rather than in Notepad or Excel.
One way or another this data needs to be transferred to a hidden Excel worksheet for further processing. So I am trying to devise a way to copy the data from the web page and paste it onto the Excel worksheet. Here is the relevant code from within the Web Browser's class module:
If URL Like "https://www.fedex.com/FedExMMA/downloadcenter.do*" Then
Application.Wait Now + TimeValue("00:00:01")
SendKeys "^a^c", False
Application.Wait Now + TimeValue("00:00:01")
ActiveWorkbook.Sheets("Sheet3").Paste Destination:=ActiveWorkbook.Sheets("Sheet3"). Range("A1")
End If
The "^a^c" (control-A, control-C) part works correctly; if I interrupt the code at that point I can open Notepad and directly paste the invoice data. But pasting into Excel fails in a way that has me baffled. Instead of the invoice it pastes the previous contents of the clipboard; for instance the invoice number that I copy and paste in an earlier step. I can even run the code as written above, see the invoice number pasted into Excel, and directly open Notepad, type Control-V, and have the actual invoice pasted into Notepad! It is as if Excel's paste function accesses different clipboard data from other applications. So my brief question is: How do I get the data into Excel? The longer question is: Is there some step I have missed that specifies to Excel what clipboard data to paste?
I'm trying to create a excel sheet with macros that will generate a tab delimitd file which is a journal voucher and I want to upload that .txt file to our financial system (SAP). When I create a tab delimited file manually, that is I enter all the values in excel and save as .txt it works great. But I have created a spreadsheet where the useres (all employees on the financial departement) enter the info required. I have a macro validating that all fields are correct and so on.. When they are finished they sedt the spreadsheet to me (an excelfile) via email, this is done by a submit buttom(macro) and the I have a hidden sheet in the worksheet which gets all the values that are necessery and then I create my tab delimited file using the well known save as fileformat=xltext macro. But SAP will not accept this file. I get error messages as convertion erros in line 4 column 6 and so on. The sheet which is saved as .txt is 100% with formulas, it gets all the value from another sheet(sheet1). I use the formula =IF(Sheet1!$E17=0;"";Sheet1!E17) in the cells with E17 changing to whatever cell is required. What can be wrong here? May it be that all the formulas (columns A to H and row 2 to 1001) somehow mix it all up and even to the cell is empty is the formula is somehow hidden in the .txt file and that messes it all up??
View 2 Replies View RelatedI 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?
On 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.
Is 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