# Formula to remove left most characters of a text string

#### nightracer

##### Board Regular
Hi I have a text string that is:

1. John Smith 27:03.2
29. Joe Public 28:04.6
105. Father Christmans 26:49.2

I simply need a formula to remove the left most characters so that I start at the name
i.e. removes 1.[space] and 29.[space] etc respectively

As can be seen, I have other decimal places in the string so cant use text to columns, and sometimes I need to remove 3 characters, sometimes 4, maybe 5.

Is there a simple formula that will leave me with:

John Smith 27:03.2
Joe Public 28:04.6
Father Christmas 26:49.2

Many thanks for any help.

Last edited:

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
The following should work(at least for the sample provided)

Excel 2013
ABC
11. John Smith 27:03.2John Smith 27:03.2John Smith 27:03.2
229. Joe Public 28:04.6Joe Public 28:04.6Joe Public 28:04.6
3105. Father Christmans 26:49.2Father Christmans 26:49.2Father Christmans 26:49.2
Sheet6
Cell Formulas
RangeFormula
B1=MID(A1,FIND(".",A1)+2,100)
C1=RIGHT(A1,LEN(A1)-FIND(".",A1)-1)

Brilliant.

Many thanks both njimack and Momentman, just the job!

Thanks again
Nightracer

Here is one more formula for you to consider...

=REPLACE(A1,1,FIND(" ",A1),"")

If you are copying this formula down and some of the cells could be blank, then use this version of the formula instead...

=REPLACE(A1,1,FIND(" ",A1&" "),"")

Replies
3
Views
319
Replies
10
Views
514
Replies
5
Views
249
Replies
5
Views
213
Replies
4
Views
227

1,202,987
Messages
6,052,936
Members
444,616
Latest member
novit19089

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back