Show the text in the cell furthest to the right when its the result of a formula

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
OK, my last one for today...

I've searched the net for help to this one but I've only found answers that are for the text in the cell furthest to the right of a range of cells rather than form certain cells......

Anyway, in cell E8 I need it to show the value in the furthest to the right of the cells M8, W8, AG8 & AQ8 (all of the ones I've coloured in yellow in the screenshot below) - so in this instance E8 would need to show the value from W8 which in this case is P02.

However, I cant get this to work whatsoever and I think that is because the formulas that I have found are when it needs to look at a range of cells rather that every 10th cell along the row AND because the value in cells M8, W8, AG8 & AQ8 is actually the result of a formula - I've included in row 9 all of the formulas that occur in row 8 just in case that helps with helping me.

1609174861379.png


Now ideally (and also greedily), the formula in cell E8 would be smart enough to take advantage of the fact that the cells it needs to look in are regularly spaced out at every 10 columns.

Anyway, as usual all help would be appreciated.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
again, it didn't return a valid answer/
Which formula?
If you are referring to the second one that I posted, are you sure that you confirmed it with Ctrl+Shift+Enter, not just Enter if using anything other than Excel 365?


The formulas in some (if not all) of your blank cells appear to be returning single spaces
Hi, I am unable to see that in either the image in post 1 or the XL2BB mini-sheet in post 3. Can you indicate what makes you say that?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,467
Office Version
  1. 365
Platform
  1. Windows
I noticed it when I was evaluating a formula to see why it was not working as expected. Tried again on a clean sheet in case it was a copy / paste error but same result.
Note that I used Alt + Click to copy the XL2BB sheet in post 3. You will see below that a number of cells which appear empty have a length of 1 character. Checking any of these in the formula bar shows a single space.

I didn't realise before, but you can also see the difference between the empty cells and those with a space in a desktop browser by using Ctrl + a on the XL2BB mini sheet.

Book1 (version 1).xlsb
DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
8RF0100 01-Oct-2015-Oct-20 01-Sep-2002-Sep-20 P0116-Sep-2018-Sep-2002-Oct-2001-Oct-20 P0215-Oct-2015-Oct-20
9615510551355000055135500001011100000101110000
Sheet5
Cell Formulas
RangeFormula
D9:AV9D9=LEN(D8)
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Note that I used Alt + Click to copy the XL2BB sheet in post 3.
Thanks for that. That is interesting. I had done the same =LEN() check and got zero for those same cells but I had used the standard Click rather than Alt+Click to copy the mini-sheet data to my worksheet.
Given that the formula in V8 cannot produce a space character (because the T8 formula cannot) I think we have to assume there is a glitch with XL2BB?
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,467
Office Version
  1. 365
Platform
  1. Windows
I think you might be correct Peter, to be honest I didn't trace the formulas back (which appears quite obvious given that I asked what was in J7 even though it is in the XL2BB capture provided).

Errors and omissions aside, thinking of simple ways to cure the problem having now looked at the formula chain.
Excel Formula:
=CHOOSE(COUNT(K8,U8,AE8,AO8)+1,"",M8,W8,AG8,AQ8)
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I have raised it as an XL2BB issue to be investigated to see if it can be rectified in a future update.
@jasonb75
FYI: In fact, @smozgur has fixed it already, including retrospectively for existing XL2BB mini-sheets. 👏👏👏
It still looks like a space if you do the Ctrl+A on the forum page, but copying to your worksheet with Alt+Click does not produce a space any more.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,896
Members
416,141
Latest member
Bartek9q

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
Top