Thanks:  0
Likes:  0

# Thread: Formula to copy partial content of a cell?

1. How about a User-Defined Function?

If you put this into a module in your workbook, you can use the function like this:

=ExtractNum(A1)

Code:
```Public Function ExtractNum(rng As Range) As Double

Dim strTemp As String

strTemp = rng.Text
Do While Not IsNumeric(Left(strTemp, 1))
strTemp = Right(strTemp, Len(strTemp) - 1)
Loop

On Error Resume Next
ExtractNum = Val(strTemp)

End Function```
Hope this helps,

Russell

2. WOW! this is getting huge!

Why not just:

=SUBSTITUTE(D14,"kg","")*1

Or

=SUBSTITUTE(SUBSTITUTE(D2,"kg",""),"Weight=","")*1

If it actualy contains the text "Weight=" as well.

3. On 2002-03-12 09:45, Russell Hauf wrote:

If you put this into a module in your workbook, you can use the function like this:

=ExtractNum(A1)

Code:
```Public Function ExtractNum(rng As Range) As Double

Dim strTemp As String

strTemp = rng.Text
Do While Not IsNumeric(Left(strTemp, 1))
strTemp = Right(strTemp, Len(strTemp) - 1)
Loop

On Error Resume Next
ExtractNum = Val(strTemp)

End Function```
Hope this helps,

Russell
Note that if your cell has 2 numbers in it that are separated by text (ex: Weight=90kg20mg), it will only return the first number.

-rh

4. WOW!

But why not

=SUBSTITUTE(D14,"kg","")*1

Or

=SUBSTITUTE(SUBSTITUTE(D2,"kg",""),"Weight=","")*1

5. Oops, double post, sorry.

6. On 2002-03-12 08:19, Anonymous wrote:
Question:
I am wondering is there an excel formula to copy one partial content of a cell to another cell?
eg. Copy 90 from Weight-90 in a cell to another cell
Care to post a few examples of text values from which you want to extract the num part?

It's important to know whether there is any kind of regularity in the entries, which could be exploited by a formula.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•