Solved

VBA that will replace line skips with commas

Posted on 2010-08-20
4
423 Views
Last Modified: 2012-05-10
I have a bunch of cells with line skips in them, as shown below only with the square symbol displayed after each number. I need a simple code that will convert all of them to commas followed by a space, as in: 17A, 17K, 18A ... etc.

Thanks,
John




... NO POWER 15H
17A
17K
18A
18C
18G

Open in new window

0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 14

Expert Comment

by:peetjh
ID: 33487853
I believe you can go to edit and replace and copy the symbol you want to remove and paste it into the find what and replace it with the comma.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33488436
John,A simple formula will do it, assuming you mean ANSI 10 for the "line skip" (that's what Excel uses for an in-cell line break).=SUBSTITUTE(A2,CHAR(10),", ")In code you could use the Replace method, as below.Patrick
ActiveSheet.UsedRange.Replace Chr(10), ", "
 
' note how the Excel function is CHAR while the VBA function is Chr  :)

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 33686580
Yes, please award the points to Patrick, his answer works perfectly.
Thanks,
John
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question