Hi Guys,
It is possible to reverse Text To Columns action after I made some changes in text?
The code is like this:
Please Login or Register to view this content.
Hi Guys,
It is possible to reverse Text To Columns action after I made some changes in text?
The code is like this:
Please Login or Register to view this content.
What do you mean by reverse?
Do you mean get back to the original string of text? Or create a new string with your changes.
If the latter I think in Excel 2019 there' a new Concatenate() function that will do that. Otherwise one way would be to
1. In a cell enter =CONACETNATE(TRANSPOSE(B1:Z1))
2. F2, F9 and Enter so that you end up with ={your values}
3. In the formula bar rempove the = and { } characters
4. Use Find & Replace to Fins " and replace with nothing.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
1. In your code you have:, why do you use "ConsecutiveDelimiter:= False" instead of "ConsecutiveDelimiter:= True" ?Please Login or Register to view this content.
2. What is the order of separators: tab is first, or semicolon is first ?
Please Login or Register to view this content.
Following on #2 an easier way assuming text columns are B1:Bnn
In B2 (assuming A2 is blank) enter
Formula:Please Login or Register to view this content.
and copy across. In the last cell Copy the string and paste as values
The text who must be splitted is like thisAfter using the code frome above the text is splitted in columns without " and ;Please Login or Register to view this content.
After I replace some unnecessarily quotation mark from the text who dosent respect the rule mentionate above I must reverse TextToColumns action to load the file in some financial program. I tryed to do this manually but there is 85 columns who must be concatenated and is hard to do, if I miss just one symbol the programe give me error.
Last edited by zGod; 05-17-2019 at 01:02 PM.
1. There are no 'tab' characters in this text and "Tab:=True" is unnecessary
2. Which of the quotations marks are not needed in the above text ?
3. To replace/change these characters for what, for an empty string, others characters ?
4. Attach a sample of such txt file without any confidential data or change this data to false data
Last edited by mjr veverka; 05-17-2019 at 05:11 PM.
You can use the Join method to concatenate a range of cells in one row.
e.g. say you have row1 filled in columns A, B & C then next piece of code will concatenate in Range("D1")
Insert a column before column 1.
Then put this in a loop and change the 3 in Resize to the number of columns to concatenate you might get the desired result.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
I do not know if it will be necessary at all to import data from the "txt" file to Excel.
Maybe it is enough to temporarily download content to memory, delete unneeded characters, and then return the text to a file.
But for this the sample "txt" file would be useful with the data layout closest to the original.
This is how the text looks (only a part of it):In this text I have to delete only the quotes before and after EU.Please Login or Register to view this content.
Eu text is not a standard value, may variate from one file to another. To find this quotes I copy all the text from .txt file to excel, I use TextToColumns with ; delimiter and " text qualifier. I am stuck to reverse TextToColumns action, find below the code written so far.
My ideea was to import the .txt file in excel, then use TextToColumns with ; delimiter and " text qualifier, remove unnecersally guotes, then reverse TextToColumns action and save the sheet to a .txt file.
Please Login or Register to view this content.
Last edited by zGod; 05-21-2019 at 09:41 AM.
Is this "EU" always in the fourth column from the end (or in the 21th column counting from the beginning) ?
(the columns are defined here by semicolons):
a) "DPR";"805525474";19129;09.05.19;"A";"Tranzaction";"A";0;"TRF";"24"; => 10 columns
b) 373,00;0;69;09.05.19;"A";"A";"190";"4567421, date";"03/03/2019, detour";"tour"; => 10 columns
c) ""EU" ABC";"0760001";"A";"A" => 4 columns
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks