Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 50
  • Last Modified:

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
0
chima
Asked:
chima
  • 4
  • 3
  • 2
1 Solution
 
CompProbSolvCommented:
Put the following in G3:
=left(B3,find(",",B3)-1)

and copy it to G5, G7, etc.
0
 
ProfessorJimJamCommented:
put this formula anywhere for example in C2 and drag down  please see atached.

=IF(MOD(ROWS($B$2:B2),2),"",TRIM(LEFT(RIGHT(SUBSTITUTE(B2,",",REPT(" ",250)),500),250)))
xcel-contractors.xlsx
0
 
chimaAuthor Commented:
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"
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chimaAuthor Commented:
Prof, you read my question correctly.  thanks.
0
 
ProfessorJimJamCommented:
you are welcome chima
0
 
chimaAuthor Commented:
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.
0
 
ProfessorJimJamCommented:
i did not understand your latest question?  did you see the file i uploaded?  didn't it work?
0
 
ProfessorJimJamCommented:
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.
0
 
CompProbSolvCommented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now