Tab Text Delimiting not working

[nterhaar89]

Hello,

I have a workbook where the data appears to be tab delimited but isn't working in Text-to-Columns.

I'm looking for help making text to columns work or other manual "left" "right" "mid" formulas to get it done. Workbook sample below where the data in Row 2 corresponds to the following header in Row 1. All data is currently in Column A.

Txn Date = 01-APR-2019
Item = 00CAT01030
Description = FG, FLOWPORT I
Source Type = Inventory
Source = BLANK (but there is data in Rows 4-10, and 15-20)
Subinv = SMMNREP1
Quantity = -1.00
UOM = EA
Unit Cost = 15.01415
Txn Value = -15.01

A problem with manual formulas is difference in "Item" length and absence of data for "Source" that makes the text string variable length.

Sample below - I'm not allowed to attach, and copy/paste text doesn't show the true spacing? Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
There are some add-ins available here that enable you to include sample data in your post https://www.mrexcel.com/forum/about-board/508133-attachments.html
However in this instance a file would be a better idea, whilst you cannot upload files to the board, you can upload a file to a share site such as OneDrive, DropBox, GoogleDrive. Mark for sharing & then post the link you receive to the board.

 

[nterhaar89]

Fluff,

Thanks for the helpful note. Here is a OneDrive link to sample data:

https://drive.google.com/open?id=151B1MnylbbYK7x5qeVRcSq4KbbU44HYv

 

[Fluff]

It looks to be Fixed With rather than tab delimited.
Select Text to columns > Fixed width > Next > check that the break lines are in the right place (there's on at 69 that looks like it should be removed) > Next > select column 2 in the data preview > Check the Text option > Finish
You can also record a macro whilst doing that to get the code.

 

[nterhaar89]

Well, that makes me feel silly. In Excel, the columns don't appear to be aligned neatly - but then when you go into text to columns view, they do.

Do I need to do anything to mark as "solved?"

 

[Fluff]

No, we don't mark threads as solved here.