Extract text and numbers

[avishal007]

I want extract numbers and text from

LAXMI GANPATI ENTERPRISES19.475

In column one LAXMI GANPATI ENTERPRISES
In column Two 19.475

BALAMURLI ENTERPRISES
19.11

tks in advance

 

[Andrew Poulsom]

Try eg:

=MID(A1,MATCH(TRUE,INDEX(ISNUMBER(--MID(A1,ROW($1:$99),1)),),FALSE),255)+0

 

[avishal007]

Thanks its works
but if my data is as follow

LAXMI GANPATI ENTERPRISES19.475
19.45LAXMI GANPATI ENTERPRISES
116.39YOCHIT IMPEX
216.385KESHAV OILS & FOODS116.385
216.385KESHAV 116.385OILS & FOODS

its not work
pl help

 

[Peter_SSs]

Thanks its works
but if my data is as follow

LAXMI GANPATI ENTERPRISES19.475
19.45LAXMI GANPATI ENTERPRISES
116.39YOCHIT IMPEX
216.385KESHAV OILS & FOODS116.385
216.385KESHAV 116.385OILS & FOODS

its not work
pl help

Welcome to the MrExcel board!

You've given a better (more varied) set of sample data, but not the results you would be expecting this time.

 

[avishal007]

col 1-LAXMI GANPATI ENTERPRISES
col 2-19.475
col 1-19.45
col 2-LAXMI GANPATI ENTERPRISES
col 1-116.39
col 2-YOCHIT IMPEX
col 1-216.385
col 2-KESHAV OILS & FOODS
col 3-116.385
col 1-216.385
col 2-KESHAV OILS & FOODS
col 3-116.385

 

[Andrew Poulsom]

Given this data in A1:A5:

<TABLE style="WIDTH: 191pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=255 border=0 x:str><COLGROUP><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9325" width=255><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 191pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=255 height=17>LAXMI GANPATI ENTERPRISES19.475</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>19.45LAXMI GANPATI ENTERPRISES</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>116.39YOCHIT IMPEX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>216.385KESHAV OILS & FOODS116.385</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>216.385KESHAV 116.385</TD></TR></TBODY></TABLE>

Try the following formulas:

B1 =IF(ISNUMBER(LEFT(A1)+0),LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99))),"")
C1 =REPLACE(REPLACE(A1,LEN(A1)-LEN(D1)+1,LEN(D1),""),1,LEN(B1),"")
D1 =IF(ISNUMBER(RIGHT(A1,1)+0),MID(REPLACE(A1,1,LEN(B1),""),MATCH(TRUE,INDEX(ISNUMBER(--MID(REPLACE(A1,1,LEN(B1),""),ROW($1:$99),1)),),FALSE),255)+0,"")

copied down.

 

[Peter_SSs]

My attempt

Excel Workbook
	A	B	C	D
1	LAXMI GANPATI ENTERPRISES19.475		LAXMI GANPATI ENTERPRISES	19.475
2	19.45LAXMI GANPATI ENTERPRISES	19.45	LAXMI GANPATI ENTERPRISES	0
3	116.39YOCHIT IMPEX	116.39	YOCHIT IMPEX	0
4	216.385KESHAV OILS & FOODS116.385	216.385	KESHAV OILS & FOODS	116.385
5	216.385KESHAV 116.385OILS & FOODS	216.385	KESHAV OILS & FOODS	116.385
Separate Text Numbers

 

[bosco_yip]

Or, try…………

1] B1 :

=IF(ISNUMBER(--LEFT(A1)),-LOOKUP(1,-LEFT(A1,ROW($1:$99))),"")

2] C1 :

=SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,"")

3] D1 :

=IF(ISNUMBER(--RIGHT(A1)),-LOOKUP(1,-RIGHT(A1,ROW($1:$99))),"")

All formulas copied down

Regards
Bosco

 

[Peter_SSs]

3] D1 :

=IF(ISNUMBER(--RIGHT(A1)),-LOOKUP(1,-RIGHT(A1,ROW($1:$99))),"")

Bosco

What about the OPs final example in posts #3 & #5?

 

[bosco_yip]

Bosco
What about the OPs final example in posts #3 & #5?

Peter,

Thanks of your good catch

Please disregarded of my formula at Column D

Regards
Bosco