minimise space in cell with a line break in the Date format

Wad Mabbit

Board Regular
Joined
Mar 31, 2016
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
I'musing Ctrl+J to create a line break in a date format

For instance
dddCtrl+JdCtrl+Jmmm;;;@

to produce
ddd
d
mmm

However, I get a wide indent, so much of the idea is wasted, like:
ddd
d
mmm

1668151907783.png


What I'm trying to do is to get date information into a cell as narrow as possible but still easily readable. Rows with bookings are usually 3 lines deep, hence the two line breaks.

I don't know what is causing the phantom spaces/indenting, but it's counter-productive. Any help on this?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try this:
• Go into Format Cells > Alignment
• Turn Wrap text off (uncheck)
• Turn on Shrink to fit
• Turn Wrap text back on
You should now be able to shrink the column to suit the text without it turning into ####
 
Upvote 0
Solution
Try this:
• Go into Format Cells > Alignment
• Turn Wrap text off (uncheck)
• Turn on Shrink to fit
• Turn Wrap text back on
You should now be able to shrink the column to suit the text without it turning into ####
Wow, nice!
 
Upvote 0
No problem. Glad I could help.
Well, if you want a challenge, I'm trying to set up a multiple conditional lookup, but it's doing my head in!
I'm starting off with the info here:

But just can't get it to work!
In the attached, row 519, I can look up Caz next lesson with this formula in K519"\:
=IF(G519="", "",IFNA(LEFT(TEXT(INDEX(A520:A$1731,MATCH(G519,G520:G$1731,0)),"dddd"), 3),"") & IFNA(TEXT(INDEX(A520:A$1731,MATCH(G519,G520:G$1731,0)), " d/m"),"") & CHAR(10) & IFNA(INDEX(B520:B$1731,MATCH(G519,G520:G$1731,0)),"") )

It gets me:
Thu 24/11
2:15-3:15

So it uses column G (Phone number) and finds the next future instance.

What I'm trying to do is to find a future instance that also has "LC" within text in row E (Details), e.g. Caz's next lesson that is also a test ('LC' stands for Lic encing Centre'), based upon the phone number (unique identifier).

If I can get that, then I think that I can work out how to test for "HPT" (Hazard Perception Test) anywhere in column E (Details) with that number match (Caz) so that I check if I have noted that Caz has done a HPT befopre the actual test day. I'm using column N for that.


Also, further bookings? It would be nice to have a list. Is that possible?. I'm using column S for that.
 

Attachments

  • Multi-lookup.jpg
    Multi-lookup.jpg
    162.7 KB · Views: 11
Upvote 0
That's a very different question and will need a separate thread. You are more likely to get a response and get a better answer if you include an XL2BB of some sample data. (I can't even see LC in your screenshot although I may have missed it)
 
Upvote 0
That's a very different question and will need a separate thread. You are more likely to get a response and get a better answer if you include an XL2BB of some sample data. (I can't even see LC in your screenshot although I may have missed it)
That's fine, and yes, I haven't put LC in a cell. I'll creste a nrw thread and try XL2BB, though I haven't had luck with that yet.
 
Upvote 0

Forum statistics

Threads
1,223,626
Messages
6,173,415
Members
452,514
Latest member
cjkelly15

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