Splitting a cell with carriage returns in it...

P

PhilipLawrence99

New Member
Joined
Aug 11, 2002
Messages
20
hi there, am trying to split cells that have an address in them which is separated by carriage returns (i.e. address1, address2, town, county. I'm more than happy to convert the cells to comma separated values but am struggling to know how to do it. The cells (and there are plenty of them) vary in length as each address is different. Look forward to hearing from you soon.......

Regards,

Phil

_________________
Philip Lawrence
This message was edited by PhilipLawrence99 on 2002-11-11 11:48
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
I imagine you're using the Text To Columns wizard, right ?

If so, then, in the "Other" field, press your Left "Alt" key, and in the Numeric keyboard type

010

if that doesn't appear to work, try with the number

013

that should do what you need.
 
Upvote 0
Ta for this but I'm using a laptop and the "ALT" 010 or 013 doens't seem to work in the wizard. I've got a cut down version of the spreadsheet if that's any use.... Cheers, Phil
 
Upvote 0
On 2002-11-11 11:49, PhilipLawrence99 wrote:
Ta for this but I'm using a laptop and the "ALT" 010 or 013 doens't seem to work in the wizard. I've got a cut down version of the spreadsheet if that's any use.... Cheers, Phil
Click to expand...
Hi Phil,
You can use a formula to substitute all CR to commas:

If your data is in A1 -put in B1:

=SUBSTITUTE(A1,CHAR(10),",")

Eli
 
Upvote 0
THat's nearly got it working but for some bizarre reason I'm still left with a single CR between the end of the work and the comma. Sorry to be a pain.....

Phil
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Split cell with Carriage returns, then loop code for only specific sheets
Replies
8
Views
897
jdellasala
J
F
  • Solved
return "1" IF a word in cell is with red font
Replies
3
Views
485
ferrigeu
F
L
  • Solved
Small VBA code to split cells if...
Replies
3
Views
829
Joe4
Joe4
P
  • Question
Dynamic approach to calculating charges against nominal codes (single value, multi value, range & combo)
Replies
0
Views
235
PureBluff
P
D
  • Question
VLOOKUP/TEXTJOIN (or similar) to return multiple unique results in a single cell
Replies
2
Views
579
DessertDiva
D

Forum statistics

Threads
1,215,387
Messages
6,124,637
Members
449,177
Latest member
Sousanna Aristiadou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top