formula with a irregular field

Question

I have a big file with just one column A the only thing in common is that each line has 133 characters on it. I try to use Text to Columns but did not work because the spacing is not the same. I try to count until the first empty character but did not work either since some elements have two empty characters. Any ideas how can I do it.
&nbsp;
AD660-C&nbsp; FBH7005N-20 GS1744 AD655&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AD660&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 36 7695000&nbsp; &nbsp; &nbsp; AD655&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AF540 -&gt;A FBNJ007-22&nbsp; WBN AF540 -BKS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AF540&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 36 7257462&nbsp; &nbsp; &nbsp; AF540WBN&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;AG535-3-2-1 -@ FBC8683W-22 FBC8683B-22 FBC8683O-22&nbsp; WBC F615-S-T-U -@&nbsp; &nbsp; &nbsp; &nbsp;AG535&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;605 7696669&nbsp; &nbsp; &nbsp; F615WBC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp;
ResultsRow1AD660-CFBH7005N-20GS1744AD655AD6607695000AD655Row2AF540 -&gt;AFBNJ007-22WBNAF540 -BKSAF540367257462AF540WBN&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Row3AG535-3-2-1 -@FBC8683W-22 FBC8683B-22 FBC8683O-22WBCF615-S-T-U -@AG5356057696669AF540WBN&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;F615WBC

sergeibaklan · Answer

Hi Fernando,
&nbsp;
Sorry, I didn't catch what you try to do - split on different columns or what?
And better if you attach sample file with few records.
&nbsp;
Thank you

fernando salgueiro · Answer

I am trying to split in columns but the data don’t follow a pattern like the 3 samples I place with the results

sergeibaklan · Answer

If copy/paste the text from your post into the Excel the words in text are separated not only by spaces, but with combination of CHAR(160)&amp;CHAR(32) as well.
&nbsp;
However it's bit hard to understand is there some logic or that's series of copy/pasting effect.&nbsp;

fernando salgueiro · Answer

Is there any formula to count from the first character until the first empty character without counting the empty? The number of empty’s and the next set of characters and so on to see if I can find a pattern?
Spoiler (Highlight to read)&nbsp;&nbsp;

willy lau · Answer

I know you tried Text to column feature in excel.&nbsp; However, in that feature, did you check the box next to "Treat&nbsp;consecutive delimiliers as one"?

Forum Discussion

formula with a irregular field

8 Replies

Resources