change data in a cell

hi,

I have a spread sheet that looks like this
TOP/BASE/SHELF 364X445
TOP/BASE/SHELF 364X445
TOP/BASE/SHELF 364X445
TOP/BASE/SHELF 764X445
TOP/BASE/SHELF 764X445
TOP/BASE/SHELF 764X445
TOP/BASE 764X499
TOP/BASE 764X499
TOP/BASE 764X499
FIXED SHELF 264X304
FIXED SHELF 264X304
364X448 INT FIXED SHELF
364X448 INT FIXED SHELF
364X448 INT FIXED SHELF
470X2600 SHELF
470X2600 SHELF
470X2600 SHELF

I would like to have all the descriptions first the all the numbers at the back which includes the x. the sixe of the text could vary

thanks

steve

Originally Posted by stevekirk

hi,

I have a spread sheet that looks like this
TOP/BASE/SHELF 364X445
TOP/BASE/SHELF 364X445
TOP/BASE/SHELF 364X445
TOP/BASE/SHELF 764X445
TOP/BASE/SHELF 764X445
TOP/BASE/SHELF 764X445
TOP/BASE 764X499
TOP/BASE 764X499
TOP/BASE 764X499
FIXED SHELF 264X304
FIXED SHELF 264X304
364X448 INT FIXED SHELF
364X448 INT FIXED SHELF
364X448 INT FIXED SHELF
470X2600 SHELF
470X2600 SHELF
470X2600 SHELF

I would like to have all the descriptions first the all the numbers at the back which includes the x. the sixe of the text could vary

thanks

steve

I suppose that your data is in col A and starts from cell A1
put in B1 and copy it down

=IF(LEN(IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1))=LEN(IF(LEN(A1)=LEN(IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1)),A1,RIGHT(A1,LEN(A1)-LEN(IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1))-1))),IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1),IF(LEN(A1)=LEN(IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1)),A1,RIGHT(A1,LEN(A1)-LEN(IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1))-1))&" "&IF(ISNUMBER(MID(A1,1,2)*1),MID(A1,1,FIND(" ",A1)-1),A1))

let me know about results.

Regards.

If your data is not in col A then copy this function from here and paste it in cell B1. then copy cell B1 and paste it to the right cell from where your data starts.

Regards.

hi,

if the numbers are at the front
i get an NAME result

steve

Originally Posted by stevekirk

hi,

if the numbers are at the front
i get an NAME result

steve

Hi steve,

Try this formula, move data down to start in A2, then put this in B2 and copy down

=IF(IF(ISNUMBER(MID(LEFT(A2,1),1,1)*1),LEFT(A2,1)*1,"")="",A2,IF(ISNUMBER(IF(ISNUMBER(MID(LEFT(A2,1),1,1)*1),LEFT(A2,1)*1,"")),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))&" "&LEFT(A2,8),""))

Let me know if this is OK

oldchippy

hi
all ok thanks
one problem is shown below.
if no space between the number and string you get VALUE .if youm then put hte space in you get the firstletter at the back


1234x1 ewqeqw ewqeqw 1234x1 e
1234x1ewqeqw #VALUE!

but there was only ten like this

thanks for all your help

steve

Originally Posted by stevekirk

hi
all ok thanks
one problem is shown below.
if no space between the number and string you get VALUE .if youm then put hte space in you get the firstletter at the back


1234x1 ewqeqw ewqeqw 1234x1 e
1234x1ewqeqw #VALUE!

but there was only ten like this

thanks for all your help

steve

Glad to be of help steve, if it's only ten you had the easy bit.