Excel help - Text to number

[leosayer]

Hi guys, I need to convert a column containing text in the format "£123.45 DR " into values that can be used for sums etc. Any ideas how this can be done, short of editing each cell or writing a macro. None of the functions seem to cover this. Thanks.

[Big Al.]

Hi Leo, can’t help on this occasion other that to offer you a comprehensive exec formula book.

Unfortunately it will not be until this evening, when I get home.

If you would like a copy drop me a mail via profile and I'll forward it on to you.

[TheGroover]

If there is always a space between the amount and the DR or CR then you can do the following.
Select the whole column then click on Data > Text to columns. A dialog box will open. On step 1 select the Delimited option, On step 2 put a tick in the space option, Step 3 should show you how the data will be split, which in your case should be a column of currency amounts and a column of CR or DR text.
You will now need to convert one of the CR or DR amounts to negatives, to do this select a cell next to the Text and enter a formula like =IF(B1="CR",A1,0-A1) and copy it down the column (In this case all DR amounts will be negative).
Hope this helps!

>> Edited by TheGroover on Monday 15th December 10:37

[leosayer]

That's it , cheers Groover (and BigAl)- I spent ages looking for that yesterday.

[TheGroover]

leosayer said:

That's it , cheers Groover (and BigAl)- I spent ages looking for that yesterday.

No problem mate
If you need to do this again try using this macro which I wrote 'cos I'm bored !

Sub ConvertToNumber()

Dim strCellValue As String
Dim curCellNewValue As Currency

ActiveSheet.Range("A1").Select 'Cell in which to start

Do While ActiveCell.Value <> "" 'go down the column until you reach a blank cell
strCellValue = ActiveCell.Value

If Right(strCellValue, 2) = "CR" Then 'This will make CR values negative.
'Amend to DR if required
curCellNewValue = 0 - CCur(Left(strCellValue, Len(strCellValue) - 2))
Else
curCellNewValue = CCur(Left(strCellValue, Len(strCellValue) - 2))
End If

ActiveCell.Value = curCellNewValue

ActiveCell.Offset(1, 0).Select
Loop
End Sub