0

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?

Improve this question
2
  • 2
    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 support.microsoft.com/en-gb/help/269370/…. Commented Dec 4, 2019 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. Commented Dec 4, 2019 at 16:09

2 Answers 2

Reset to default
3

Try using Format$.

If InStr(stringVar, Format$(numberVar, "0")) > 0
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Format is another way to do this. Nice :)
2

Use CDec. Is this what you want?

Debug.Print CDec(numbervar)

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.