UDF Function or Macro Needed

[qcan]

Hi,

I've tried all kinds of things, but this problem is beyond me...
Ideally I need a UDF function or a small macro to be created.

The facts are:....

- Every cell in a particular column(s) has multiple characters
(string) at the begining with a numbers(s) at the end.
- The characters could possibly have spaces.
- The numbers might have a negative sign proceeding the number itself
or a decimal or both.

Problem is:

I need this column to be split into two columns with one containing
the characters and the other containing the number.

Example:

CELL A1 Contains: ABSHHF KJGG -1524.001

The function would put ABSHHF KJGG into cell A2 and -1524.001 into
cell A3.

That's it.

Thanks,

 

[Stefi]

- The numbers might have a negative sign proceeding the number itself

or a decimal or both.

Is "or none of them" a possible case?

Stefi


„qcan†ezt írta:

 

[Peter T]

Select cells in a single column and run the macro.

Two assumptions:
- there's always text to left and a number to right
- there's always a space between the text and number

Sub TxtLt_NumRt()
Dim pos As Long
Dim s As String
Dim cell As Range

For Each cell In Selection
s = cell
pos = InStrRev(s, " ")
If pos Then
cell.Offset(0, 1) = Left$(s, pos - 1)
cell.Offset(0, 2) = Right$(s, Len(s) - pos)
End If
Next

End Sub

Revert if cannot rely on the assumptions.

Something similar could be done with a UDF but only if array entered in
pairs of cells.

Regards,
Peter T

 

[Stefi]

I mean e.g. ABSHHF KJGG 1524.001 is a possible case?
Stefi


„qcan†ezt írta:

 

[paul.robinson]

Hi
Some cell formulas. If your string is in A1 then in B1 put

=IF(ISERROR(FIND("-",A1)),CLEAN(LEFT(A1,FIND(".",A1)-1)),CLEAN(LEFT(A1,FIND("-",A1)-1)))

and in C1 put

=IF(ISERROR(FIND("-",A1)),CLEAN(RIGHT(A1,LEN(A1)-
FIND(".",A1)+1)),CLEAN(RIGHT(A1,LEN(A1)-FIND("-",A1)+1)))

You can then fill these down.
The first formula looks to see if it can find a "-". If it can't it
finds the position of the first "." and returns the string to the left
of the dot without any trailing space. If it can it returns the same
string to the left of the first "-".
The second formula does the same except from the right.

regards
Paul

 

[Ron Rosenfeld]

Hi,

I've tried all kinds of things, but this problem is beyond me...
Ideally I need a UDF function or a small macro to be created.

The facts are:....

- Every cell in a particular column(s) has multiple characters
(string) at the begining with a numbers(s) at the end.
- The characters could possibly have spaces.
- The numbers might have a negative sign proceeding the number itself
or a decimal or both.

Problem is:

I need this column to be split into two columns with one containing
the characters and the other containing the number.

Example:

CELL A1 Contains: ABSHHF KJGG -1524.001

The function would put ABSHHF KJGG into cell A2 and -1524.001 into
cell A3.

That's it.

Thanks,

Here are two UDF's that will extract the string portion or the numeric portion
of the entry.

It returns a #NUM! error if the string does not contain a number at the end.

If the format is different than what you describe, it may need some minor
tweaking. For example, if the last character(s) is not a digit.

==============================
Option Explicit
Function NumAtEnd(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[\-+]?\d*\.?\d+$"
If re.test(str) = True Then
Set mc = re.Execute(str)
NumAtEnd = CDbl(mc(0))
Else
NumAtEnd = CVErr(xlErrNum)
End If
End Function
'------------------------------------
Function GetString(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[\-+]?\d*\.?\d+$"
GetString = Trim(re.Replace(str, ""))
End Function
=====================================
--ron

 

[qcan]

Hi,

I've tried all kinds of things, but this problem is beyond me...
Ideally I need a UDF function or a small macro to be created.

The facts are:....

- Every cell in a particular column(s) has multiple characters
(string) at the begining with a numbers(s) at the end.
- The characters could possibly have spaces.
- The numbers might have a negative sign proceeding the number itself
or a decimal or both.

Problem is:

I need this column to be split into two columns with one containing
the characters and the other containing the number.

Example:

CELL A1 Contains: ABSHHF KJGG -1524.001

The function would put ABSHHF KJGG into cell A2 and -1524.001 into
cell A3.

That's it.

Thanks,

Thank you everybody for your effort & time. Special thanks to Peter T.
You NAILED it the 1st time. It's FLAWLESS.