need space between Uppercase

[sksanjeev786]

Hi Team,

I need a space between names and want to remove the numbers

Book1
	A	B
1	SanjeevKumar990	Sanjeev Kumar
2	RajKumar1038	Raj Kumar
3	VishalKumarSingh221	Vishal Kumar Singh
4	Anay342	Anay
Sheet1

Regards
Sanjeev

 

[offthelip]

try this macro:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
For i = 1 To lastrow
 txt = ""
 For j = 1 To Len(inarr(i, 1))
   tt = Mid(inarr(i, 1), j, 1)
   If Asc(tt) > 64 And Asc(tt) < 91 And j > 1 Then
    txt = txt & " "
   End If
   If Asc(tt) < 58 Then
    Exit For
   End If
   txt = txt & tt
  Next j
  Range(Cells(i, 2), Cells(i, 2)) = txt
 Next i
End Sub

 

[sksanjeev786]

try this macro:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
For i = 1 To lastrow
 txt = ""
 For j = 1 To Len(inarr(i, 1))
   tt = Mid(inarr(i, 1), j, 1)
   If Asc(tt) > 64 And Asc(tt) < 91 And j > 1 Then
    txt = txt & " "
   End If
   If Asc(tt) < 58 Then
    Exit For
   End If
   txt = txt & tt
  Next j
  Range(Cells(i, 2), Cells(i, 2)) = txt
 Next i
End Sub

Hi,

Thank you so much for your help on this.

This works perfectly...

Just wanted to check can we have these in the Excel formula

i was trying to apply with the below one but not getting the result

=IF(AND(EXACT(PROPER(LEFT(A1,FIND(" ",A1)-1)),LEFT(A1,FIND(" ",A1)-1)),EXACT(PROPER(RIGHT(A1,LEN(A1)-FIND(" ",A1))),RIGHT(A1,LEN(A1)-FIND(" ",A1))))=TRUE,LEFT(A1,FIND(" ",A1)-1)&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

Regards
Sanjeev

 

[Sufiyan97]

Try

Book2
	A	B	C
1	SanjeevKumar990	Sanjeev Kumar
2	RajKumar1038	Raj Kumar
3	VishalKumarSingh221	Vishal Kumar Singh
4	Anay342	Anay
5
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(REPLACE(IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)),SEARCH(" ",IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)))+2),1),0," "),IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)))

 

[sksanjeev786]

Try

Book2
	A	B	C
1	SanjeevKumar990	Sanjeev Kumar
2	RajKumar1038	Raj Kumar
3	VishalKumarSingh221	Vishal Kumar Singh
4	Anay342	Anay
5
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(REPLACE(IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)),SEARCH(" ",IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)))+2),1),0," "),IFERROR(REPLACE(LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),2),1),0," "),LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1)))

Wow!!

work perfectly

Thank you team for your support on this

Regards
Sanjeev

 

[Sufiyan97]

You're welcome.

 

[offthelip]

Looking at the two solutions I was interested to note that the vba solution was 294 characters while the formula solution was 1095 characters. I have never manage to write an equation that long!! Probably because I find it easier to write in VBA

 

[sksanjeev786]

Looking at the two solutions I was interested to note that the vba solution was 294 characters while the formula solution was 1095 characters. I have never manage to write an equation that long!! Probably because I find it easier to write in VBA

yes! Thank you so much for your time this.....

Yes formula has multiple conditions due to that it is long

 

[Sanjeev1976]

Simpler version of the same formula :

=LET(a,LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),b,CHAR(ROW($65:$90)),c,AGGREGATE(15,6,FIND(b,a,2),1),IFERROR(REPLACE(IFERROR(REPLACE(a,c,0," "),a),AGGREGATE(15,6,FIND(b,IFERROR(REPLACE(a,c,0," "),a),SEARCH(" ",IFERROR(REPLACE(a,c,0," "),a))+2),1),0," "),IFERROR(REPLACE(a,c,0," "),a)))

 

[sksanjeev786]

Simpler version of the same formula :

=LET(a,LEFT(A1,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)-1),b,CHAR(ROW($65:$90)),c,AGGREGATE(15,6,FIND(b,a,2),1),IFERROR(REPLACE(IFERROR(REPLACE(a,c,0," "),a),AGGREGATE(15,6,FIND(b,IFERROR(REPLACE(a,c,0," "),a),SEARCH(" ",IFERROR(REPLACE(a,c,0," "),a))+2),1),0," "),IFERROR(REPLACE(a,c,0," "),a)))

Thank you so much sir for providing the help on this