Separate number and text

[Thomas Kuriakose]

Respected Sirs,

We have a cost element report which has the element and description in the same cell. We need to split the same in cells as shown in the required columns D,E and F.

I have tried to split using the below - but not ale to get the required result.

1. =LEFT(A2, FIND(" ",A2))
2. =RIGHT(A2, LEN(A2)-FIND(" ",A2))

Kindly guide on how to get the results as per the required columns. Kindly find attached the sample file.

Thank you very much,

with regards,
thomas

 

[Khalid NGO]

Hello Thomas,
Good day...

Please try these

D2:
=IFERROR(LEFT(A2, FIND(" ",A2))+0,"")

E2:
=TRIM(IF(ISNUMBER(LEFT(A2)+0),MID(A2, FIND(" ",A2),LEN(A2)),""))

F2:
=TRIM(IF(ISNUMBER(LEFT(A2)+0),"",A2))

Copy down all...

Regards,

 

[Thomas Kuriakose]

Respected Sir,

Good day to you too.

This is amazing, thank you so much for the solution provided. This worked perfectly.

Much appreciated.

Sir, in the same exercise, we have made a pivot table and written a formula outside the pivot table to get results on change of pivot filter selection. The results are getting mapped for the complete texts and not for the cells which have text and number -
The below is the formula used outside the pivot table
=IF(C5*(B5<>"Grand Total"),INDEX(Lookup!$H$2:$H$133,MATCH(B5,Lookup!$A$2:$A$133,0)),"")

Kindly let me know where we are going wrong on this.

Thank you very much once again,

with regards,
thomas

 

[Khalid NGO]

Hi Thomas,
Glad it helped.

Please share example.

Regards,