formula with a irregular field

[Fernando Salgueiro]

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.

AD660-C  FBH7005N-20 GS1744 AD655                                           AD660                    36 7695000      AD655           
AF540 ->A FBNJ007-22  WBN AF540 -BKS                                        AF540                    36 7257462      AF540WBN        
AG535-3-2-1 -@ FBC8683W-22 FBC8683B-22 FBC8683O-22  WBC F615-S-T-U -@       AG535                   605 7696669      F615WBC         

Results

Row1
AD660-C
FBH7005N-20

GS1744
AD655
AD660
7695000
AD655
Row2
AF540 ->A
FBNJ007-22
WBN
AF540 -BKS
AF540
36
7257462
AF540WBN        
Row3
AG535-3-2-1 -@
FBC8683W-22 FBC8683B-22 FBC8683O-22
WBC
F615-S-T-U -@
AG535
605
7696669
AF540WBN        
F615WBC

[#2]

Hi Fernando,

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.

Thank you

[#3]

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

[#4]

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)&CHAR(32) as well.

However it's bit hard to understand is there some logic or that's series of copy/pasting effect. 

[#5]

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

[#6]

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

[#7]

Yes

[#8]

Is there a formula that count the number of characters before the first empty field? So i can find a logic?

---

@Sergei Baklan wrote:

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)&CHAR(32) as well.

However it's bit hard to understand is there some logic or that's series of copy/pasting effect. 

---

AD660-A  FBH1560-20  WBH R565-Z                                             AD660                   400 7695000      R565WBH         

B1 = first word 7 characters 

c1 = two empty space

d1 = 10 characters 

e1 = two empty spaces 

f1 = 3 characters 

G1 = one space 

h1 = 6 characters 

etc 

I1 = 45 empty field 

[#9]

I am sorry.  Perhaps another community member can help.  However, as far as I know, Excel cannot have a logic to extract data for Row 1, and using the same logic, to extract data for Row 3 in your original post, especially, the first cell of Row 3:

AG535-3-2-1[You want a space here]-@