Check if String contains certain Number without CStr conversion - Excel VBA

Question

Is it possible to check if a number exists in a string using VBA? Say I have a number - 137785973201908000000000 - and it is in a cell formatted as Number, how do I convert it to a string to use the InStr function?

At the moment, if I use CStr(number) it will convert it to - 1.37785973201908E+23 - which is not ideal. The string I am trying to find the number in looks like this - [137785973201908000000000] Product Shipped.

When using If InStr(stringVar, CStr(numberVar)) > 0 Then it will not find the number because it is converting it to the scientific notation when converting to text.

Is there VBA coding that can stop this?

Do you expect 137785973201908000000001 being a valid input? Because it will be truncated if the input are formated as number in Excel. You might want to store the data as text from the start. See https://support.microsoft.com/en-gb/help/269370/last-digits-are-changed-to-zeroes-when-you-type-long-numbers-in-cells. — Vincent G, Dec 04 '19 at 14:42
+1 for storing your number as Text - Excel can only support 15 digits of precision, so if your number is larger than that you will lose information. — Tim Williams, Dec 04 '19 at 16:09

score 3 · Accepted Answer · answered Dec 04 '19 at 14:07

3

Try using Format$.

If InStr(stringVar, Format$(numberVar, "0")) > 0

answered Dec 04 '19 at 14:07

BigBen

46,229
7
24
40

1

Format is another way to do this. Nice :) – Siddharth Rout Dec 04 '19 at 14:44

score 2 · Answer 2 · answered Dec 04 '19 at 14:33

2

Use CDec. Is this what you want?

Debug.Print CDec(numbervar)

answered Dec 04 '19 at 14:33

Siddharth Rout

147,039
17
206
250

Check if String contains certain Number without CStr conversion - Excel VBA

2 Answers2