Extract only text from given range

[uday]

Hi,

I am trying to extract only text from column A,B,C,D.

i.e., for the first row, it should be Uday. I have attached excel file herewith.

Can someone please assist me?

Thanks
Uday

 

[bosco_yip]

Hi,



Maybe….................

In E2, copied down :

=MID(IF(ISNA(A2),"",", "&A2)&IF(ISNA(B2),"",", "&B2)&IF(ISNA(C2),"",", "&C2)&IF(ISNA(D2),"",", "&D2),3,99)

Regards
Bosco

 

[rahulshewale1]

Hi @uday,

OR ..
E2
=TEXTJOIN(",",TRUE,IF(ISNA(A2:D2)=FALSE,A2:D2,""))


Regard
Rahul shewale

 

[uday]

Thanks for your reply. Can you please explain why you used 3 and 99 end of the mentioned formula?

Its working anyway.

Thanks

 

[uday]

Hi @uday,

OR ..
E2
=TEXTJOIN(",",TRUE,IF(ISNA(A2:D2)=FALSE,A2:D2,""))

Regard
Rahul shewale

Its giving me #NAME error rahul

 

[rahulshewale1]

uday,

textjoin funtion available only office 365 and office 2016.


Regard
Rahul shewale

 

[uday]

uday,

textjoin funtion available only office 365 and office 2016.


Regard
Rahul shewale

Ok. Mine is 2019

 

[rahulshewale1]

Hii,


For more information .

https://support.office.com/en-gb/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

https://exceljet.net/excel-functions/excel-textjoin-function

 

[GraH - Guido]

@uday ,
if you have 2019, then according to Microsoft it should work.

TEXTJOIN function
Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2019 for Mac More...
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

Note: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

 

[Peter Bartholomew]

I too used the TEXTJOIN() function but reversed the test by using ISTEXT()
= TEXTJOIN( ", ", TRUE, IF( ISTEXT(Title), Title, "" ) )
where 'Title' is a relative reference to the current row of titles.
For me this took the form
=Table1[@[Title1]:[Title4]]