Converting all data in the cells of a column to the same type using a macro

moninah 0
Viewed 46

How can I automatically convert this data to column A in a worksheet with a macro. So that when I paste (CTRL + V) into the cells of column A, this data is automatically converted?

For example:

F8 300m
N5/6/7 140m
650m X5
235m S2
L3 270M
500m FMA
Nest 475m
340m Pr6
720M uT10
etc.

Should automatically convert to:

F8 300m
N5/6/7 140m
X5 650m
S2 235m
L3 270M
FMA 500m
Nest 475m
Pr6 340m
uT10 720M
etc.

That is, it is necessary that (any number)m and (any number)M be on the right.

vba excel
1 Answers

The following formula would work without needing VBA as long as there is always a space and as long as the code before or after the (number)m never ends in an m:

=IF(RIGHT(A1,1)="m",A1,MID(A1,FIND(" ",A1,1)+1,LEN(A1)-FIND(" ",A1,1)) & " "& LEFT(A1,FIND(" ",A1,1)-1))
userZP5l10
Related