Forum Discussion
formula with a irregular field
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
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"?
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.
Is there a formula that count the number of characters before the first empty field? So i can find a logic?
SergeiBaklan 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
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]-@
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?
