If there is a number in cell put % symbol in that number

[Reggie Jardin]

Hi,

I have a cell with alphanumeric character the problem is, i want to put percentage symbol in any number on that cell..

Here is a sample data in cell..

ABC 95 CDF 100 KLM 9
CD 124 AGH 29 QLK 12

That sample above is a two rows in one column on that column I want to put a % symbol in the end of every number.. any possible solution in excel or in macro?

Thanks in Advance

 

[GraH - Guido]

Expected result is this below then?
ABC 95% CDF 100% KLM 9%
CD 124% AGH 29% QLK 12%

 

[Reggie Jardin]

yes expected result is just all number will have a percentage symbol at the end

 

[bosco_yip]

Maybe,

In B2, copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&"%"," ","% ",2)," ","% ",4)," ","% ",6)

Regards
Bosco

 

[Reggie Jardin]

View attachment 57574

Maybe,

In B2, copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&"%"," ","% ",2)," ","% ",4)," ","% ",6)

Regards
Bosco

Hi,

Wow Thank You
Almost done but if data is like this

80 ABCDEFG 15 HIJKL 5 MNOP LININ: 100 WXYZ

RESULT IS

80% ABCDEFG 15% HIJKL 5 MNOP LININ: 100 WXYZ%

notice that the % is not coming in middle and in last number also, no % symbol, instead it went on the last portion....

 

[Reggie Jardin]

Please see attached output file for the same

 

[vletm]

Reggie Jardin
From original case
i want to put percentage symbol in any number on that cell..
... there were two possible solutions (without clear rules)

Usage: Select ONE cell and press one of [ Do It ]-button.

 

[Reggie Jardin]

Reggie Jardin
From original case
i want to put percentage symbol in any number on that cell..
... there were two possible solutions (without clear rules)

Usage: Select ONE cell and press one of [ Do It ]-button.

Hello,

Thank You so much,

Your second macro is working but it's only in active cell.. is it possible that when i select the entire column the macro will run instead of one by one cell?

It's perfect...

 

[Reggie Jardin]

Hi,

Wow Thank You
Almost done but if data is like this

80 ABCDEFG 15 HIJKL 5 MNOP LININ: 100 WXYZ

RESULT IS

80% ABCDEFG 15% HIJKL 5 MNOP LININ: 100 WXYZ%

notice that the % is not coming in middle and in last number also, no % symbol, instead it went on the last portion....

 

[vletm]

Reggie Jardin
Both marcos should work as You wrote.

About Your question ...
Yes, it's possible ...
... and it would be already done (column case) if there would be mention of that.
But, seems that Your changed hopes would need something else who knows?

 

[John Jairo V]

Hi, to all!

I leave an UDF to make this task:

Function PerRightNumber$(S$)
  Dim sep, i&
  sep = Split(S)
  For i = 0 To UBound(sep)
    If IsNumeric(sep(i)) Then sep(i) = sep(i) & "%"
  Next i
  PerRightNumber = Join(sep)
End Function

Just use like a formula:
=PerRightNumber(A1)

And drag it down.

Check file. Blessings!

 

[Reggie Jardin]

HI

Hi, to all!

I leave an UDF to make this task:

Function PerRightNumber$(S$)
  Dim sep, i&
  sep = Split(S)
  For i = 0 To UBound(sep)
    If IsNumeric(sep(i)) Then sep(i) = sep(i) & "%"
  Next i
  PerRightNumber = Join(sep)
End Function

Just use like a formula:
=PerRightNumber(A1)

And drag it down.

Check file. Blessings!

Wow this is superb.. Thank You So Much ^_^