Need basic help with cells in excel

[collierlaw]

Please help! I have been working on a project since Monday and I am not very good with Excel, so I am having all sorts of issues doing basic stuff.

The Cells I have look like this:

A1: B1:

WRIGHT, CARLY DENISE 1234 SURREY LN FOLEY AL 36535

What I need the cells to look like:

A1: B1: C1: D1:
Wright Carly Denise 1234 Surrey Lane Foley, AL 36535

Please help!

 

[oldbrewer]

first find the location of the comma assume WRIGHT, CARLY DENISE 1234 SURREY LN FOLEY AL 36535 is in A1
in say T1 =search(",",A1) this will return 7
U1 = T1-1 = 6

in B1 =left(A1,6) = wright (but instead of 6 you would put T1)
there is a space to the right of the comma so mid(A1,(7+1),1 = C ( of CARLY)

proceed like that

 

[collierlaw]

I am sorry, it came out a little messed up when I typed it the first time. Here is what it looks like:

a1:
WRIGHT, CARLY DENISE

B1:
1234 SURREY LN FOLEY AL 36535

I need it to look like:

a1:
Wright

B1:
Carly Denise

C1:
1234 Surrey Lane

D1:
Foley, AL 36535

Thank you for your help, oldbrewer.

 

[Gilliam]

I have A way of solving your problem, pretty darn robust if you ask me though!

It'll do for a quick fix, but a more efficient strategy is advised.

So the formulas I'm about to show need to be inputted into other cells, and then copied and pasted over the originals obviously.
in cell G1 for instance:
*finds the comma and replaces everything to the right of it with nothing*
=REPLACE(trim(A1),FIND(",",trim(A1)),255,"")
H1:
*find the starting position of the comma, adds one to it and removes everything to the left of that*
=SUBSTITUTE(trim(A1),LEFT(trim(A1),FIND(",",trim(A1))+1),"")
I1:
*finds the 3rd position of " ", and returns everything to the left of it*
=SUBSTITUTE(trim(B1),RIGHT(trim(B1),LEN(trim(B1))-(FIND(" ",trim(B1),(FIND(" ",trim(B1),FIND(" ",trim(B1),1)+1))+1))),"")
J1:
*again finds the 3rd instance of " " and returns everything to the right of it.
=MID(trim(B1),(FIND(" ",trim(B1),(FIND(" ",trim(B1),FIND(" ",trim(B1),1)+1))+1))+1,255)

you can probably see why it is so important to use Trim() because of all the spaces we need to account for, granted it would be much easier for you to have a cell with just trim(A1) and Trim(B1)

 

[oldbrewer]

C1:
1234 Surrey Lane

D1:
Foley, AL 36535

it could be surrey avenue or surrey street
it could be little foley
it could be acacia road, bampton, guildford gu12 5tt

so you need to add markers where you want to slice the originals eg
acacia road, bampton, # guildford gu12 5tt