1

I'm have a column with cells containing data like:

PY=486776347 PY 7031493729

I'm trying to dynamically remove everything after and including the space in this instance, but it'd be useful to know how to do this for any character.

I tried this, with no success:

Columns("P:P").Replace What:=" ""*", Replacement:="", LookAt:=xlPart

and

[P:P].Replace What:=" ""*", Replacement:="", LookAt:=xlPart 'should be effectively the same function as above
aLearningLady
  • 1,988
  • 4
  • 24
  • 42

1 Answers1

1

I think you just need to add (&) like:

Columns("P:P").Replace What:=" " & "*", Replacement:="", LookAt:=xlPart

Or :

Columns("P:P").Replace What:=" *", Replacement:="", LookAt:=xlPart

We can replace the space with any character, for example 3:

Columns("P:P").Replace What:="3*", Replacement:="", LookAt:=xlPart

The result is: PY=486776

Fadi
  • 3,302
  • 3
  • 18
  • 41
  • Perfect, thorough answer. Thank you! Though, out of curiosity, what if I wanted to remove everything after the second space instead? – aLearningLady Jan 24 '16 at 03:48
  • @Dr.Trey, You are welcome, if all data in this format (there is `PY` before the second space) we can use `Columns("P:P").Replace What:="PY *", Replacement:="PY", LookAt:=xlPart`. and if not maybe we need to use loop `For Each Cell In range`. – Fadi Jan 24 '16 at 06:01