100M = 100,000,000; 100k = 100,000

[jomni]

I want to make a macro where you just input 100m and the data automatically converts to 100,000,000, etc. Anyone has some leads? Examples?

[mangesh_yadav]

Enter the following code in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Right(Target.Value, 1)) = "M" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) * 1000000
ElseIf UCase(Right(Target.Value, 1)) = "K" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) * 1000
End If

End Sub


Mangesh

[Alan]

"mangesh_yadav"
<mangesh_yadav.1qnlyd_1118815523.7323@excelforum-nospam.com> wrote in
message
news:mangesh_yadav.1qnlyd_1118815523.7323@excelforum-nospam.com...
>
> Enter the following code in the sheet module:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If UCase(Right(Target.Value, 1)) = "M" Then
> Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
> 1000000
> ElseIf UCase(Right(Target.Value, 1)) = "K" Then
> Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
> 1000
> End If
>
> End Sub
>
>
> Mangesh
>
>
> --
> mangesh_yadav
> --------------------------------------------------------------------
----
> mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> View this thread:
http://www.excelforum.com/showthread...hreadid=379235
>

Very cool!

I would consider putting an error check in there, else if you enter,
say, "Mum" in a cell it will error out.

However, still very cool!

Alan.

[mangesh_yadav]

Hi Alan,

That depends on the OP (whether to have any validations or not), but yes, it would give an error in that case for sure. Thanks.

Mangesh

I would consider putting an error check in there, else if you enter,
say, "Mum" in a cell it will error out.

However, still very cool!

Alan.

[Alan]

"jomni" <jomni.1qnj6b_1118811966.1255@excelforum-nospam.com> wrote
in message news:jomni.1qnj6b_1118811966.1255@excelforum-nospam.com...
>
> I want to make a macro where you just input 100m and the data
> automatically converts to 100,000,000, etc. Anyone has some leads?
> Examples?
>

Hi Jomni,

Shouldn't be too difficult.

I would suggest you use a worksheet_change event to trigger a check on
the taget cell (or cells) and pattern match where the entry is a text
string of
the form "[N]m" where [N] is one or more numeric digits.

HTH,

Alan.

[Dr. Solver]

Dear Jomni.
The maximum characters # is 15 and it accurate; example, if you inter
123,123,123,123,123,123 that’s 18 characters, excels will save it as 15
character only as: 123,123,123,123,123,000.But that rarely happen.

"jomni" wrote:

>
> I want to make a macro where you just input 100m and the data
> automatically converts to 100,000,000, etc. Anyone has some leads?
> Examples?
>
>
> --
> jomni
> ------------------------------------------------------------------------
> jomni's Profile: http://www.excelforum.com/member.php...fo&userid=7744
> View this thread: http://www.excelforum.com/showthread...hreadid=379235
>
>