I'm having problem with a big set of Excel data. One others had inputted the data like this:
A
10
10:12
11:12:15
My task is to convert it to something like this:
B
Pig
Pig:Koala
Dog:Koala:Bird
I was trying to use substitute:
= SUBSTITUTE(A1, "10", "Pig")
But the problem is, Excel recognizes those value in A column as other data types (number, time...) and the SUBSTITUTE doesn't work on those types.
How could I fix this issue?
Thank you.
This function will return a string that matches what excel is displaying.
Option Explicit
Function ToText(r As Range) As String
If r.Count <> 1 Then
ToText = "#ERR!"
Exit Function
End If
ToText = IIf(r.NumberFormat = "General", CStr(r.Value), Format(r.Value, r.NumberFormat))
End Function
for example, if 10:11:12 is in A1, which excel thinks is a time, and is formatted this way, then =ToText(A1) will return the string 10:11:12, which you can then manipulate as you would any other text
put this into a module on the spreadsheet ( ALT + F11 ) so the function is available to excel
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments