Formula for getting the Left most cell value from a 'Range of cells'

Question

Need your support in finding a solution for an issue which I am facing now.

Formula for Column A... Data Range from B to D

Have values in a range of spreadsheet cells (From column B to D)
Value may or may not be present within the range
I need the formula to update the leftmost value from the same row to the Column A.. E.g In Row 2, "Abil" is the leftmost cell from the range B2 to D2.

Something like =Left(B2:D2) which will select the leftmost cell value to the A2 cell

user0 · Accepted Answer · 2019-07-27 11:54:09Z

0

=ARRAYFORMULA(IF(IF(B2:B<>"", B2:B, C2:C)<>"", 
                 IF(B2:B<>"", B2:B, C2:C), D2:D))

answered Jul 27, 2019 at 11:54

user0

26.2k8 gold badges49 silver badges78 bronze badges

1

Thanks @user0.. It worked perfectly resolving all my issues!
– Jackson Gilbert
Jul 27, 2019 at 12:31

Add a comment |

user0 · Accepted Answer · 2019-07-27 12:37:23Z

0

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE(B2:D), , 999^99))), "\w+")))

=ARRAYFORMULA(INDEX(IFERROR(SPLIT(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE(B2:D), , 999^99))), " ")), , 1))

answered Jul 27, 2019 at 11:48

user0

26.2k8 gold badges49 silver badges78 bronze badges

Thank you "User0" ... It worked...
– Jackson Gilbert
Jul 27, 2019 at 11:57
Only issue which I faced is, in few cells now I am having URLs as the cell value... E.g hxxps://webapps.stackexchange.com @user0 The formula you provided only shows "hxxts" with in the cell triming off the remaining section.. SO how can we get the full value within the cell without checking the value within the cell & trimming text within ?
– Jackson Gilbert
Jul 27, 2019 at 12:07
@JacksonGilbert answer updated
– user0
Jul 27, 2019 at 12:37
The last updated array formula is very much easy and perfect... Appreciate your support...
– Jackson Gilbert
Jul 27, 2019 at 12:46

Add a comment |

2 Answers 2