Extract text and numbers

avishal007

New Member
Joined
Nov 28, 2009
Messages
8
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

avishal007

New Member
Joined
Nov 28, 2009
Messages
8
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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. :biggrin:
 
Last edited:

avishal007

New Member
Joined
Nov 28, 2009
Messages
8

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My attempt

Excel Workbook
ABCD
1LAXMI GANPATI ENTERPRISES19.475 LAXMI GANPATI ENTERPRISES19.475
219.45LAXMI GANPATI ENTERPRISES19.45LAXMI GANPATI ENTERPRISES0
3116.39YOCHIT IMPEX116.39YOCHIT IMPEX0
4216.385KESHAV OILS & FOODS116.385216.385KESHAV OILS & FOODS116.385
5216.385KESHAV 116.385OILS & FOODS216.385KESHAV OILS & FOODS116.385
Separate Text Numbers
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

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