how do i separate numbers and text in a cell?

R

Rick Rothstein \(MVP - VB\)

example a) Site: 3480 test data migration
1) Example a is just a number in the string, all number one numbers are
always at the end of the string, but not always between parentheses. I
also
found situation with a space between the opening parentheses and the
number.
2) Below are the formats I could expect. The abbreviations represent
worked
hours in English and Dutch.

X hr
X hrs
X uur

Xhr
Xhrs
Xuur

x.x hr
x.x hrs
x.x uur

x.xhr
x.xhrs
x.xuur

hopes this answer the question?
Click to expand...

I think this macro function will extract the numbers you are looking for...

Function ExtractNumber(ByVal StringIn As String) As Single
Dim Extraction() As String
On Error Resume Next
StringIn = Replace(StringIn, "(", "( ")
StringIn = Replace(StringIn, "hr", Chr$(1))
StringIn = Replace(StringIn, "uur", Chr$(1))
Extraction = Split(StringIn, Chr$(1))
Extraction = Split(RTrim$(Extraction(UBound(Extraction) - 1)), " ")
ExtractNumber = CSng(Extraction(UBound(Extraction)))
End Function


Rick
 
G

Guest

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0))+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
G

Guest

OOPS! I forgot to post a significant part.....

Cells E2:F13 contain these values:

hr 5
hr 4
hr) 4
hr) 5
hrs 4
hrs 5
hrs) 5
hrs) 6
uur 4
uur 5
uur) 5
uur) 6

Note that some of the text begins with a space.

***********
Regards,
Ron

XL2002, WinXP
 
R

Rick Rothstein \(MVP - VB\)

Try something like this:
Using Col_B as a "helper column":
B1:
=LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0))+1)

Copy B1 down through Bxxx

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through Cxxx
Click to expand...

I am newly returned to Excel, so I am very rusty still... however, Jan said
he has 30,000 rows of data... wouldn't using a VBA macro be better than
loading up all those formulas into the spreadsheet directly?

Rick
 
G

Guest

ron, all results prints "na". is it correct the data in column B is equal to
column A?
 
G

Guest

i assume you meant F2:G13? i see in column B the changes, however column c
prints "na" for all rows.
 
G

Guest

The Col_B values should match the Col_A values LESS the matched pattern on
the right side.

Example:
A2: Home: Porterbrook / Ravers investigation (2 uur)
B2: Home: Porterbrook / Ravers investigation (2

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
G

Guest

ron, it works :) copy past from the forum to Excel caused an additional
character.
by using e:f referencing the different abbreviations and the number of
characters to delete i should be able to retreive my data.

thanks.
 
G

Guest

Hi, Rick

Since my posted formulas don't use an array formulas or incrementally
expanding formulas (eg COUNTIF($A$1:$A2,"whatever") ), the performance hit
is minimal.

I tested the formulas in a 30,000 row range....then sorted the first column
ascending/descending. The recalcs each took less than 2 seconds.

A UDF might look more elegant, but they are usually relatively sluggish.
I'm not sure it could match that same performance level. Consequently, it may
not be worth having the annoying Macro Warning if that's the only code in the
workbook.

***********
Regards,
Ron

XL2002, WinXP
 
G

Guest

I'm glad that helped.......Thanks for letting me know.

***********
Regards,
Ron

XL2002, WinXP
 
R

Rick Rothstein \(MVP - VB\)

A UDF might look more elegant, but they are usually relatively sluggish.
I'm not sure it could match that same performance level.
Click to expand...

Ah, I see what you mean. The UDF I developed (maybe it could be made a
little more efficient, however it) took 19 seconds to process a 30,000 row
sample I created (as opposed to your under 2 second solution).

Rick
 
G

Guest

Ron,

could you validate the next
- the text string is "Amey - intern support development environment; 1 uur"
- the result after applying your solution is "Amey - intern support
development environment; 1" however the result gives an error. i verified the
text string with similair lines but cannot find the solution. i also used the
option "evaluate formula" which shows we have a value error. again i cannot
find the difference. Please assist.
 
D

Dave Gibson

This bit of VBA should sort you out.

As I don't know where your data resides on your worksheet :
1. Copy your data and paste in cell A1 of a new workbook or worksheet
2. Copy the code below and paste in the VBE
3. Run the macro "GetHours"
4. Your hours should now be in Col B

---------------------------------------------------
Sub GetHours()

Dim r As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Columns("A")
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
..Replace What:=" hr", Replacement:="hr", LookAt:=xlPart
..Replace What:=" uur", Replacement:="hr", LookAt:=xlPart
..Replace What:=" hrs", Replacement:="hr", LookAt:=xlPart
..Replace What:="hrs", Replacement:="hr", LookAt:=xlPart
End With

r = Range("A1").CurrentRegion.Rows.Count - 1

With Range(Range("B1"), Range("B1").Offset(r, 0))
..FormulaR1C1 = _
"=IF(ISERR(FIND(""hr"",RC[-1])),0,MID(RC[-1],FIND(""hr"",RC[-1])-1,1))"
..Select
End With

Selection.Value = Selection.Value
Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
-------------------------------------------------
Regards

Dave Gibson
Leeds U.K.


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
D

Dave Gibson

The code I posted above is not showing correctly.
As there is no option here to correct the post I am attaching the correct code below.

---------------------------------------------------

Sub GetHours()

Dim r As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Columns("A")
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
..Replace What:=" hr", Replacement:="hr", LookAt:=xlPart
..Replace What:=" uur", Replacement:="hr", LookAt:=xlPart
..Replace What:=" hrs", Replacement:="hr", LookAt:=xlPart
..Replace What:="hrs", Replacement:="hr", LookAt:=xlPart
End With

r = Range("A1").CurrentRegion.Rows.Count

With Range(Range("B1"), Range("B1").Offset(r, 0))
..FormulaR1C1 = _
"=IF(ISERR(FIND(""hr"",RC[-1])),0,MID(RC[-1],FIND(""hr"",RC[-1])-1,1))"
..Select
End With

Selection.Value = Selection.Value
Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
D

Dave Gibson

The code is still not showing corrrectly!
The formula shown in the code after FormulaR1C1 = _
should be :

=IF(ISERR(FIND(""hr"",RC[-1])),0,MID(RC[-1],FIND(""hr"",RC[-1])-1,1))

surounded by double quotes "......"

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

extract text from string, leaving numbers and operators 13
Splitting text and numbers from a cell 6
separation of text and numbers in an unmerged cell 9
Converting Text String to Separate Numbers 1
Macro to add numbers in a text string 5
sum and count text and numbers same column 1
Formula to separate text and numbers 6
Formula to return a value for a portion of a text string in a cell 2

Top