Sort/Filter issue

T

terri

We have a problem. We have a long string of data that had sections separated
by spaces. We want to pull out the first section and the third section.
Problem is there not all the same length. Example below

B360N V ORB 505 123
FB350N V FLA 505

So we want to pull out the B360N and the FB350N and the ORB & FLA into two
columns.

Does anyone have any suggestions --

We can do in access or excel?
 
P

Peo Sjoblom

One way

=LEFT(A2,FIND(" ",A2)-1)

copy down, when you are done copy the range and then do edit>paste special
as values if you want the result to be independent of the data
 
M

Max

One way which might suffice ..
Source data assumed in A1 down
In B1: =LEFT(A1,FIND("V",A1)-1)
In C1: =MID(A1,FIND("V",A1)+2,3)
Select B1:C1, copy down
 
J

Jennifer

Perhaps you can highlight the column and try "text to columns" under menu
selection Data... choose delimited and "spaces" as your delimiter.
 
B

bj

try
=LEFT(A24,FIND(" ",A24)-1)

=MID(A24,FIND(" ",A24,FIND(" ",A24)+1)+1,FIND(" ",A24,FIND(" ",A24,FIND("
",A24)+1)+1)-FIND(" ",A24,FIND(" ",A24)+1)+-1)
 
Top