asked on

MS Excel formatting

Hello,
The attached file has the city value/text on a separate line. I wish to move that information into a separate column.
Tired an = , much to my surprise it is not working. Why?
Ideally would like to have the City only in a separate column. I hate to ask to fix the file, yet I'm in a rush to find a contractor.
Please do tell me how you fixed it. I need to sort it.
xcel-contractors.xlsx

CompProbSolv

Put the following in G3:
=left(B3,find(",",B3)-1)

and copy it to G5, G7, etc.

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chima

ASKER

CompProbSolv, There is something wrong with the file or column because when I paste the formula in the cell, it does nothing, it just shows; =left(B3,find(",",B3)-1) The format of the cells is "text"

chima

ASKER

Prof, you read my question correctly. thanks.

Professor J

you are welcome chima

chima

ASKER

I would like to know what other change is needed, because I did paste this; =IF(MOD(ROWS($B$2:B2),2),"",TRIM(LEFT(RIGHT(SUBSTITUTE(B2,",",REPT(" ",250)),500),250))) and all I see is the formula.
I got the file, I have to go find a contractor.

Professor J

i did not understand your latest question? did you see the file i uploaded? didn't it work?

Professor J

put an equal sign then paste then after the equal sign IF(MOD(ROWS($B$2:B2),2),"",TRIM(LEFT(RIGHT(SUBSTITUTE(B2,",",REPT(" ",250)),500),250))) then press enter and then drag the formula down.

CompProbSolv

The formula works in my copy of your spreadsheet. Any chance you've got something extra at the start of the cell, such as a quote character?