Align Data Center and Top

Cartillo used Ask the Experts™
Hi Experts,

I would like to request Experts help. How to make data in “Order (B3: ABD146)” and “Report (C6: I149)” sheets are automatically aligned to Center and Top and at the same time removing a gap between the data in the cells.

Currently the data only visible when  double click the cells. Hope Experts will help to add this feature.  Attached the workbook for Experts perusal.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It appears that those fields just have a newline space that you will need to delete from each one.

When you copy one of the cells to Notepad for example, it copies like this:


Title LLB
Title TTT

Title VVV"

Where you want it like this:

"Title LLB
Title TTT
Title VVV"

Excel only provides an easy way to remove newlines altogether, which you don't want to do.

If you hit "Ctrl+H" and then in the Find field hold Alt and then type 0 1 0 on the keypad, and hit replace it will become: "Title LLBTitle TTTTitle VVV" (

You could replace the newlines with ", " so it would become "Title LLB, Title TTT, Title VVV" if that would work for you.

You can use a utility like ASAP tools to replace double newlines if you need to do this non-manually:
Sub alighdata()
For Each cel In Range("C6:I149")
Do While InStr(cel, vbCrLf & vbCrLf) > 0
cel.Value = Replace(cel, vbCrLf & vbCrLf, vbCrLf)
If Left(cel, 1) = vbCr Then cel.Value = Right(cel, Len(cel) - 1)
Next cel
End Sub


Thanks for the solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial