Number format in cells

[Pettor]

Hello guys,

Is there any VBA way that I could use to convert text format to number?
I am pasting some data from a web page, and some of the numbers are pasted with a dot instead of a comma before the decimals (pages' fault).
Then I use a macro to replace the dot with the comma but the cell is still left in a text format resulting in a problem when I am trying to feed the MS Access.
The columns that I would like this to be applied are the F, H, J, L:AK.

Many thanks

 

[DRSteele]

I hate to muddy the waters here. I just know that pasting from the web, even from this forum, often means pasting rogue characters. Below is a cool website that lists all the UNICHAR codes. Navigate to the General Punctuation section (8192-9203) on the left menu and use the arrow navigators to drive around. You can see all the weird blank codes, like 8203. Those are the ones that can be used by web pages and Excel doesn't see them as blanks - it sees them as just something miscellaneous and won't display them. In my case, C5 had a 4 in it as text, but LEN(c5) showed a length of 2! The character is there, but can't be seen. Even Power Query can't seem to get rid of it with clean and trim operations.

So try all the other 32 blank codes between 8192 and 8303 to see if you can find the culprit.

Unicode Symbol Table

Unicode Converter: Animated Unicode Symbol Table providing interactive search and copying.

www.grogllc.com

Fluff wrote some code to get rid of 8203. Maybe you can use it to rid yourself of whichever is your villain.

 

[Pettor]

You can find the file here with the data pasted and before applying the code.

Test

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[Pettor]

Test

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[jasonb75]

After many failed attempts, I think that I have a solution that works.

Sub Formation()
  Dim r As Long, rng As Range, rCol As Range
  
  Application.ScreenUpdating = False
  For r = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 2 Step -1
    If IsEmpty(Cells(r, "AL").Value) Or Cells(r, "AL").Value Like "[[]*]" Then
      Rows(r).Delete
    ElseIf IsEmpty(Cells(r, "AK").Value) Then
      Cells(r - 1, "AM").Value = Cells(r, "AL").Value
      Rows(r).Delete
    End If
  Next r
With Range("AL2", Range("AL" & Rows.Count).End(xlUp))
  .Replace What:=")", Replacement:="", LookAt:=xlPart
  .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2))
End With
Set rng = Intersect(ActiveSheet.UsedRange, Range("C:J, L:AK"))
  With rng
    .Replace What:=".", Replacement:=",", LookAt:=xlPart
    .Replace What:="-", Replacement:="", LookAt:=xlWhole
    .Replace What:="-2", Replacement:="", LookAt:=xlWhole
    For Each rCol In rng.Columns
        rCol.TextToColumns Destination:=rCol, DataType:=xlDelimited, textQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Next
  End With

  With Intersect(ActiveSheet.UsedRange, Range("I:I, K:K"))
    .Replace What:="Ü", Replacement:="á", LookAt:=xlPart
    .Replace What:="Ý", Replacement:="å", LookAt:=xlPart
    .Replace What:="Þ", Replacement:="ç", LookAt:=xlPart
    .Replace What:="ß", Replacement:="é", LookAt:=xlPart
    .Replace What:="À", Replacement:="ú", LookAt:=xlPart
    .Replace What:="ü", Replacement:="ï", LookAt:=xlPart
    .Replace What:="ý", Replacement:="õ", LookAt:=xlPart
    .Replace What:="þ", Replacement:="ù", LookAt:=xlPart
  End With


  Application.ScreenUpdating = True
End Sub

 

[Pettor]

Oh, you are great man. I thought I had to forget about it. Let me check and revert.

Thanks!

 

[Pettor]

The good thing is that it worked with the formation but it replaced all the values in I & K columns with "uuu" which is not the good part...

 

[Pettor]

Oh wait, I just saw that the copy and paste between the systems changed the accented characters that have to be replaced.
Give me a moment to change only the specific part of the code.

 

[Pettor]

Finally, it seems to be working perfectly fine!

What was the problem? Was it about roque characters?

Thank you very much! You saved me from some lost data!

 

[jasonb75]

There were no rogue characters, for some reason the copy and paste of an empty cell didn't seem to work as I thought it should, although I'm sure that I've used it before with no issues. Perhaps there are differences between the methods that work on dot decimals and comma decimals I doubt that we will ever find the cause but at least a solution was possible.

I've used the text to columns method to convert text to numeric quite a few times, but as it only works with single columns I tried a few other things first, when none of them worked, I went back and looped through the columns to do each one individually.

 

[Pettor]

Thank you very much! I really appreciate it!