How Do I Cut & Paste Data from One Column to the Next?

[sayme]

Hi guys.. Wanted to know how to cut and paste only the prices at the end of the cells in column B into cells in column C. I tried using 'Text to Columns.' But it only works when cutting and pasting data to the left of cells not to the right when data is aligned to the left. Please let me know. Thanks.

 

[Michael M]

In column C2 paste this and drag down as required

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),255))

 

[Peter_SSs]

If you want those prices to be numerical so that other calculations might be done with them, then I would use this amendment

=RIGHT(SUBSTITUTE(B14," ",REPT(" ",20)),20)+0

 

[Michael M]

@Peter_SSs
Yeah, good pickup Peter

 

[sami1]

If you want those prices to be numerical so that other calculations might be done with them, then I would use this amendment

=RIGHT(SUBSTITUTE(B14," ",REPT(" ",20)),20)+0

Hi Peter. Would you mind breaking down and explaining each part of the formula please?
....

 

[sami1]

Peter, apart from asking you to break down the formula, can you amend the formula to remove 'VALUE!' that appears in empty cells? Looking forward.

 

[Michael M]

Use

=IFERROR(RIGHT(SUBSTITUTE(B2," ",REPT(" ",20)),20)+0,"")

The formula substitutes a space for 20 spaces, then extracts the last set of characters to the right.
By adding zero on the end, it makes the result an actual number rather than text.
The IFERROR should be self explanatory