• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

symbol alignment

S

smallxyz

Member
Dear all,

Is it possible for Excel to accomplish the following?



The data are contained ONLY in one cell.

Testing Data:

( Jason, Team 1, No, 500)
( Geoffrey, Team 2a, Yes, 2500)
( Coleman, Team 2b, No, 50000)

Thanks in advance!
 
Thanks Khalid.
That is an alternative.

I am thinking of writing a VBA to get around it.
But it seems to be complicated, as it involves using

Instr, chr(10), characters.insert, and array storage.

Or maybe my direction is not a smart one. :(
 
Why put to data in one cell only, you are limiting anything you may want to do with data segments.
 
Hi ,

What you want done can be done using VBA , but it will be complicated , since it will involve separately storing the data to identify the maximum length of text in each segment.

Even after this , it will work only if a fixed length font is selected , since in a proportional font , different letters have different widths , and this is different from the width of a space character.

Narayan
 
NARAYANK991 said:
Hi ,

What you want done can be done using VBA , but it will be complicated , since it will involve separately storing the data to identify the maximum length of text in each segment.

Even after this , it will work only if a fixed length font is selected , since in a proportional font , different letters have different widths , and this is different from the width of a space character.

Narayan
Click to expand...
Or do you know, other than Excel VBA, are there any other software tools which could shoot this problem?
 
sake of a better readability.
Click to expand...
To much data in one cell will cause users to scan read and miss parts of the data, data in the middle tends to be skipped. All data should be broken down to the smallest relevant part.
 
bobhc said:
To much data in one cell will cause users to scan read and miss parts of the data, data in the middle tends to be skipped. All data should be broken down to the smallest relevant part.
Click to expand...
Right. And that is the reason for the symbols to be aligned.
 
Hi ,

The point is that the data you are trying to put into one cell , actually is 4 different fields ; if you put those 4 different types of data into 4 different columns , you can do an Autofit of the data , and hide the Gridlines ; this will give the viewer access to all of the data in one go , and yet the data can be aligned as you wish because it is in different cells.

Narayan
 
NARAYANK991 said:
Hi ,

The point is that the data you are trying to put into one cell , actually is 4 different fields ; if you put those 4 different types of data into 4 different columns , you can do an Autofit of the data , and hide the Gridlines ; this will give the viewer access to all of the data in one go , and yet the data can be aligned as you wish because it is in different cells.

Narayan
Click to expand...
I have attempted before. But the result appears to be not so satisfactory when it comes to post-editing of data, as users need to click on many cells to edit one-by-one.
 
Hi ,

In that case , the only thing I can recommend is the use of a userform for data entry and editing. That way , the user sees everything they want , in the way they want , while the data can be stored in any way , either in one cell or in several cells , without the need for it to be aligned in any particular fashion.

Narayan
 
Here's one formula based idea which might be useful. It is essentially based what Narayan has mentioned above.

Yellow cells at top calculate the maximum length of characters in particular column. Modify the range to suit your needs (maximum number of rows you envisage). Yellow cell formula is Array formula so you will have to Ctrl+Shift+Enter it and not just Enter it.

In cell D3, it is normal formula having CONCATENATE,REPT and REPLACE function.

I am attaching the workbook for reference.
 

Attachments

  • FixedWidth_smallxyz.xlsx
    10 KB · Views: 7
You must log in or register to reply here.
Back
Top